SQL Server Portal

I really appreciate active participation of my blog readers. Yesterday, I received an inquiry from ‘Abhilash’ one of my blog readers, how to search a string value in all columns in the table and in all tables in a database.

Given below is the solution that will search a string value in all columns in the table and in all tables in a database.

Lets proceed it step by step.
Step 1:
Create given below table in tempdb database to consolidate all search result.

Use tempdb
GO
Create Table Result
(
[Sno] int identity(1,1),
[Database Name] sysname,
[Schema Name] sysname,
[Table Name] sysname,
[Column Name] sysname,
[Record Name] varchar(Max)
)

Step 2:
Create the given below stored procedure to search the string in all columns in the table and in all tables in a database.

--Create the store procedure to search a string across the tables.
Use AdventureWorks2012
GO
Create PROCEDURE Find_Record_Across_Tables_Proc
@Database sysname,
@Schema sysname,
@Table sysname,
@String VARCHAR(Max)

AS

DECLARE @SqlString varchar(Max)
DECLARE @Table_Schema sysname
DECLARE @Table_Name sysname
DECLARE @Column_Name sysname

--Declare Cursor
SET @SqlString = 'DECLARE String_cursor CURSOR FOR
Select TABLE_SCHEMA, TABLE_NAME ,COLUMN_NAME from
' + @Database +'.INFORMATION_SCHEMA.COLUMNS
Where DATA_TYPE IN (''text'',''ntext'',''varchar''
,''nvarchar'',''char'',''nchar'')'

--Filter schema name
IF @schema IS NOT NULL
Begin
SET @SqlString = @SqlString + ' And TABLE_SCHEMA=''' + @Schema + ''''
End
--Filter table name
IF @table IS NOT NULL
Begin
SET @SqlString = @SqlString + ' And TABLE_NAME=''' + @table + ''''
End

Print @SqlString
EXEC (@SqlString)

OPEN String_cursor

FETCH NEXT FROM String_cursor
INTO @Table_Schema, @Table_Name, @Column_Name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlString = 'IF EXISTS(SELECT ' + QUOTENAME(@Column_Name)
+ ' FROM ' + @Database + '.' + QUOTENAME(@Table_Schema)
+ '.' + QUOTENAME(@Table_Name)
+ ' WHERE ' + QUOTENAME(@Column_Name)
+ ' Like ''%' + @string + '%'')
Insert into tempdb.dbo.result
([Database Name],[Schema Name]
,[Table Name],[Column Name],[Record Name])
SELECT ''' + QUOTENAME(@Database) + ''','''
+ QUOTENAME(@Table_Schema) + ''','''
+ QUOTENAME(@Table_Name) + ''',''''
+ ''' + QUOTENAME(@Column_Name)
+ ''',' + QUOTENAME(@Column_Name)
+ ' FROM ' + @Database + '.'
+ QUOTENAME(@Table_Schema)
+ '.' + QUOTENAME(@Table_Name)
+ ' WHERE ' + QUOTENAME(@Column_Name)
+ ' Like ''%' + @string + '%'''
Print @SqlString
EXEC (@SqlString)

FETCH NEXT FROM String_cursor
INTO @Table_Schema, @Table_Name, @Column_Name

END
CLOSE String_cursor
DEALLOCATE String_cursor
GO

Step 3:
In the step 3, you can execute the above stored procedure with different parameters.
Given below are few examples of above stored procedure.

Example 1:
To search a string in all columns of all tables in a single database.

Use AdventureWorks2012
GO
Delete from tempdb.dbo.result
GO
EXEC Find_Record_Across_Tables_Proc
'AdventureWorks2012', NULL, NULL ,'Senior'
GO
Select * from tempdb.dbo.result
GO

Example 2:
To search a string in all columns of a single tables and in a single database.

Use AdventureWorks2012
GO
Delete from tempdb.dbo.result
GO
EXEC Find_Record_Across_Tables_Proc
'AdventureWorks2012', NULL, 'Employee' ,'Senior'
GO
Select * from tempdb.dbo.result
GO

Example 3:
To search a string in all columns of a single schema in a single table and in a single database.

Use AdventureWorks2012
GO
Delete from tempdb.dbo.result
GO
EXEC Find_Record_Across_Tables_Proc
'AdventureWorks2012', 'HumanResources', 'Employee' ,'Senior'
GO
Select * from tempdb.dbo.result
GO

Read Full Post »