Efficient Way To Search String Value In All Columns

In this article, we'll take a look at Hide

1) Searching a String Value in all Columns of a Table

2) Searching String in all Columns in all Tables of a Database

In this article, we want to discuss a nice function to Search String Value in all Columns of a Table in SQL Server. Before we get started, if you want to know about random number generators, please go through the following article: Random Number Generator Script – SQL Query.

One of the common questions in various SQL Server user forums is how to find information in all columns of the table or in all tables of the database for a particular column. This article expands on the solution from that original blog post and also examines several other interesting common scenarios.

Searching a String Value in all Columns of a Table

IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'spSearchStringInTable'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.spSearchStringInTable AS SET NOCOUNT ON;');
GO
  
ALTER PROCEDURE spSearchStringInTable
(@SearchString NVARCHAR(MAX),
 @Table_Schema sysname = 'dbo',
 @Table_Name sysname)
 AS
 BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)
  
-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
 ORDER BY COLUMN_NAME
 FOR XML PATH('')),1,2,'');
  
IF @Columns IS NULL -- no character columns
   RETURN -1;
  
-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
 ORDER BY COLUMN_NAME
 FOR XML PATH('')),1,2,'');
   
 SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT '
  
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
  
 WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name
 ORDER BY CU.ORDINAL_POSITION
 FOR XML PATH('')),1,9,'');
  
 IF @PkColumn IS NULL
    SELECT @PkColumn = 'CAST(NULL AS nvarchar(max))';
      
 -- set select statement using dynamic UNPIVOT
 DECLARE @SQL NVARCHAR(MAX)
 SET @SQL = 'SELECT *, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
  ' FROM
  (SELECT '+ @PkColumn + ' AS [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) +  ' ) src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
 WHERE [Column Value] LIKE ''%'' + @SearchString + ''%'''
   
 --print @SQL
  
EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString;
END
GO

IF NOT EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'spSearchStringInTable'

AND ROUTINE_TYPE = 'PROCEDURE'

)

EXECUTE ('CREATE PROCEDURE dbo.spSearchStringInTable AS SET NOCOUNT ON;');

ALTER PROCEDURE spSearchStringInTable

(@SearchString NVARCHAR(MAX),

@Table_Schema sysname = 'dbo',

@Table_Name sysname)

BEGIN

DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns

SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')

AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema

ORDER BY COLUMN_NAME

FOR XML PATH('')),1,2,'');

IF @Columns IS NULL -- no character columns

RETURN -1;

-- Get columns for select statement - we need to convert all columns to nvarchar(max)

SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')

AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema

ORDER BY COLUMN_NAME

FOR XML PATH('')),1,2,'');

SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT '

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU

ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND

TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name

ORDER BY CU.ORDINAL_POSITION

FOR XML PATH('')),1,9,'');

IF @PkColumn IS NULL

SELECT @PkColumn = 'CAST(NULL AS nvarchar(max))';

-- set select statement using dynamic UNPIVOT

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT *, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +

' FROM

(SELECT '+ @PkColumn + ' AS [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ' ) src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt

WHERE [Column Value] LIKE ''%'' + @SearchString + ''%'''

--print @SQL

EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString;

END

To test this procedure you can pass string name to search, table schema, and table name like so:

Execute dbo.spSearchStringInTable @SearchString = N'test', @table_schema = 'dbo', @table_name = 'items'

1	Execute dbo.spSearchStringInTable @SearchString = N'test', @table_schema = 'dbo', @table_name = 'items'

Searching String in all Columns in all Tables of a Database

We may want to run the above script for all tables in the database. In this case, we can use a cursor loop against all tables in the database with the following code:

IF OBJECT_ID('TempDB..#Result', N'U') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA] sysname, [TABLE Name] sysname)
DECLARE @Table_Name sysname, @SearchString NVARCHAR(MAX), @Table_Schema sysname
SET @SearchString = N'Test'
 
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT   Table_Schema, Table_Name
    FROM     INFORMATION_SCHEMA.Tables   
    WHERE TABLE_TYPE = 'BASE TABLE'
    ORDER BY Table_Schema, Table_Name
     
    OPEN curAllTables
    FETCH  curAllTables
    INTO @Table_Schema, @Table_Name   
    WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database
      BEGIN
        INSERT #RESULT ([PK COLUMN], [Column Value], [Column Name], [Table Schema], [Table Name])
        EXECUTE spSearchStringInTable @SearchString, @Table_Schema, @Table_Name
     
        FETCH  curAllTables
        INTO @Table_Schema, @Table_Name
      END -- while
    CLOSE curAllTables
    DEALLOCATE curAllTables
  -- Return results
  SELECT * FROM #RESULT ORDER BY [Table Name]