Searching and finding a string value in all columns in a SQL Server table

Create a procedure and then use it

CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
AS

BEGIN TRY
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' 
    
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = @schema
   AND TABLE_NAME = @table 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   EXEC (@sqlCommand)
   PRINT @sqlCommand
END TRY

BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
END CATCH 

How to use:

Find the value '%land%' in the Person.Address table

EXEC sp_FindStringInTable '%land%', 'Person', 'Address' 

 

Creation of string like aa,ab....ba,bb....za,zb,... zz in sql server

Creation of string like aa,ab....ba,bb....za,zb,... zz in sql server

 --------------------------------------------------------------------------------------------------------
;
with
cte_tally as
(
select row_number() over (order by (select 1)) as n
from sys.all_columns
)
select
  char(n) as alpha, ROW_NUMBER() over (order by char(n)) as SNO into #temp
from
  cte_tally
where

  (n > 96 and n < 123);

--select * from #temp

Declare @CounterOuter int=1
Declare @CounterInner int=1
Declare @CodeCounter int=100
create table #tempResult (code int ,value varchar(10))
while (@CounterOuter<=26)
begin
    while (@CounterInner<=26)
    begin
        print convert(varchar,@CounterOuter)+'-'+convert(varchar,@CounterInner)
   
        Declare @ValueOuter varchar(10)
        Declare @ValueInner varchar(10)
        set @ValueOuter=(Select alpha from #temp where SNO=@CounterOuter)
        set @ValueInner=(Select alpha from #temp where SNO=@CounterInner)
        insert into #tempResult (code,value) select @CodeCounter,@ValueOuter+''+@ValueInner
        set @CounterInner=@CounterInner+1
        set @CodeCounter=@CodeCounter+1
    end

    set @CounterOuter=@CounterOuter+1
    set  @CounterInner=1
end

 select code,value from #tempResult
drop table #temp
drop table #tempResult

Change date, month in datetime datatype column

Suppose you want to change the date, month in datetime datatype column

Ex: 1966-09-01 00:00:00 ---->>>>  1966-01-09 00:00:00

Table Name:  TableName
Column Name: dob

update TableName set  dob=convert(datetime, convert(varchar(10), convert(varchar(4),year(dob))+'-'+right('0' +convert(varchar(2),day(dob)),2)+'-'+ right('0'+convert (varchar(2),month(dob)),2)),101)



Created by Vikas Jaiswal
Contact on vikasjaiswal.ctit@gmail.com

Search Text in SP Views Functions in SQL Server

Suppose we want to search "abc" text in Stored procedure, views, function in sql then use below query: 

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'abc', @notcontain = ''

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0 
or CHARINDEX(@notcontain,syscomments.text)<>0))

Searching and finding a string value in all columns in a SQL Server table

 

Below is the procedure to find string value in all columns in sql table

Create PROCEDURE [dbo].[sp_FindStringInTable] @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS

DECLARE @sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)

BEGIN TRY
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE'
   SET @where = ''

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = ''' + @schema + '''
   AND TABLE_NAME = ''' + @table + '''
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

   EXEC (@cursor)

   OPEN col_cursor  
   FETCH NEXT FROM col_cursor INTO @columnName  

   WHILE @@FETCH_STATUS = 0  
   BEGIN  
       IF @where <> ''
           SET @where = @where + ' OR'

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + ''''
       FETCH NEXT FROM col_cursor INTO @columnName  
   END  

   CLOSE col_cursor  
   DEALLOCATE col_cursor

   SET @sqlCommand = @sqlCommand + @where
   --PRINT @sqlCommand
   EXEC (@sqlCommand)
END TRY
BEGIN CATCH
   PRINT 'There was an error. Check to make sure object exists.'
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3
   BEGIN
       CLOSE col_cursor  
       DEALLOCATE col_cursor
   END
END CATCH 

Example:

Here are some tests that were done against the Employee database.
Find the value 'John%' in the dbo.EmployeeList table.

EXEC sp_FindStringInTable 'John%', 'dbo', 'EmployeeList'

you will get rows having john in any column.

In this procedure you have to pass three parameters --- string to search, schema name, table name.
Sometimes, you do not know schema name for a table.

SELECT '['+SCHEMA_NAME(schema_id)+']'
AS SchemaTable
FROM sys.tables

Where namelike 'EmployeeList'

 

List Schema Name and Table Name for Database

 

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

First letter upper case for each word in sql server

First letter upper case for each word in sql server

I have a table on my database. My table's name is "Test". I want to change data "Column1" as upper case first letter. For example;  Data for Column1 "AbC COMPANY" as "Abc Company" To do this : Step1: Create a function CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) RETURNS VARCHAR(4000) AS BEGIN DECLARE @Index INT DECLARE @Char CHAR(1) DECLARE @PrevChar CHAR(1) DECLARE @OutputString VARCHAR(255) SET @OutputString = LOWER(@InputString) SET @Index = 1 WHILE @Index <= LEN(@InputString) BEGIN SET @Char = SUBSTRING(@InputString, @Index, 1) SET @PrevChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING(@InputString, @Index - 1, 1) END IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(') BEGIN IF @PrevChar != '''' OR UPPER(@Char) != 'S' SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char)) END SET @Index = @Index + 1 END RETURN @OutputString END GO   Step 2:  update table set column=[dbo].[InitCap](column);