SQL Server: Searching All Columns in a Table for a String

아티클
1/17/2024

Introduction

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. The latter question was recently encountered in this Transact-SQL MSDN forum question Query Error - Schema Does Not Exist.

A blog post existed for the first problem - searching all columns of a table for a string How to search a string value in all columns in the table and in all tables in the database (unfortunately, this blog no longer works as the site domain not supported by the founder Jacob Sebastian anymore - this is a great pity as it was a site with a wealth of knowledge).

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

Let's start from the problem described in the mentioned blog - given a character string we want to search all character columns in a table and return the rows containing that string. The result will give us the primary key of the row, column's name and column's value.

Let's add the following stored procedure to the database:



CREATE OR 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

This stored procedure first creates a concatenated list of all character columns of a table (e.g. [Col1], [Col2], etc.)

It also creates a primary column(s) list (in case there is a composite primary key, the columns in this key are divided with | symbol. The keys in the PK are listed in the correct order).

Finally, it gets another Cols statement converting all columns into the nvarchar(max). This is done because for the UNPIVOT operation all columns must be of the same type and size.

So, the UNPIVOT command is generated for all character columns in the table and that new column (which is called Column_Value) is checked against passed @SearchString using LIKE operator.

Then this generated SQL statement is sent to sp_ExecuteSQL system stored procedure along with the @SearchString parameter.

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'

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]

Obviously, this code may run for a long time for the database with many tables with lots of rows. So, be careful before running it.

The code above is tested on local SQL Server 2012 database and works fine. However, there may be scenarios when the code above will fail. If you ran into such scenario, please leave a comment to this article and you may add that case to this article.

Searching all Columns of a Table for Multiple Words

When reviewing that beyond the relational blog post we had two important comments to that blog. First was a code correction by Tom Cooper which is incorporated into the blog and this article's code and another was a question of searching multiple words. So, let's consider this problem also.

For this problem let's assume SQL Server 2008 and up, so we will be describing TVP (table valued parameter) solution.

Let's create a similar stored procedure that will work with the list of words to search in every character column in a table. First we need to create table type and then do the following modifications in the procedure:

IF TYPE_ID(N'SearchWords') IS  NULL
    CREATE TYPE SearchWords AS TABLE
( Word nvarchar(50) PRIMARY  KEY );
GO
 
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'spSearchWordsInTable'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.spSearchWordsInTable AS SET NOCOUNT ON;');
GO
  
ALTER PROCEDURE  dbo.spSearchWordsInTable
(@SearchWords SearchWords READONLY,
 @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 unpvt.*, ' + 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 EXISTS (SELECT 1 FROM @SearchWords SW WHERE unpvt.[Column Value] LIKE ''%'' + SW.Word + ''%'')'
   
 --print @SQL
  
EXECUTE sp_ExecuteSQL @SQL, N'@SearchWords SearchWords READONLY', @SearchWords
END
GO

And to test this new procedure let's try:

DECLARE @SWords AS SearchWords;
INSERT INTO  @SWords (Word) VALUES ('Test'), ('Reserved');
 
execute dbo.spSearchWordsInTable @SearchWords = @SWords, @table_schema = 'dbo', @table_name = 'items'

We can also test this procedure to get information for all tables in the database:

DECLARE @SWords AS SearchWords;
INSERT INTO  @SWords (Word) VALUES ('Test'), ('Reserved');
 
--execute dbo.spSearchWordsInTable @SearchWords = @SWords, @table_schema = 'Sales', @table_name = 'Store'
 
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
 
 
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 
        EXECUTE dbo.spSearchWordsInTable @SearchWords = @SWords, @Table_Schema = @Table_Schema, @Table_Name = @Table_Name
     
        FETCH  curAllTables
        INTO @Table_Schema, @Table_Name
      END -- while
    CLOSE curAllTables
    DEALLOCATE curAllTables
  -- Return results 
  SELECT * FROM #RESULT ORDER BY  [Table  Name]

This code searches all character columns for any of the words in the passed words table.

What if we want to make sure that all passed words are present in the column?

Search all Columns in a Table for all Passed Words

Let's modify the stored procedure again to search for all words inclusive using relational division principles:

IF TYPE_ID(N'SearchWords') IS  NULL
    CREATE TYPE SearchWords AS TABLE
( Word nvarchar(50) PRIMARY  KEY );
GO
 
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'spSearchWordsInTableInclusive'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.spSearchWordsInTableInclusive AS SET NOCOUNT ON;');
GO
  
ALTER PROCEDURE  spSearchWordsInTableInclusive
(@SearchWords SearchWords READONLY,
 @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 = 'DECLARE @NumberOfWords INT ;
 SELECT @NumberOfWords = COUNT(*) FROM @SearchWords;
 
 SELECT unpvt.*, ' + 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
INNER JOIN @SearchWords SW ON unpvt.[Column Value] LIKE ''%'' + SW.Word + ''%''
GROUP BY unpvt.[PK Column], unpvt.[Column Value], unpvt.[Column Name]
HAVING COUNT(DISTINCT(Sw.Word)) = @NumberOfWords'
   
 --print @SQL
  
EXECUTE sp_ExecuteSQL @SQL, N'@SearchWords SearchWords READONLY', @SearchWords
END
GO

and to test:

DECLARE @SWords AS SearchWords;
INSERT INTO  @SWords (Word) VALUES ('Test'), ('Item');
 
execute dbo.spSearchWordsInTableInclusive @SearchWords = @SWords, @table_schema = 'dbo', @table_name = 'items'

This returns just a few rows for me in the items table. The code above requires all passed words to be present in the column.

Searching Specific Column in all Tables of a Database

Finally, let's consider the problem mentioned in the beginning of this article with the MSDN forum's post reference which prompted this article.

Let's assume we know the column's name but we don't know the table's name. The solution will be similar to what we have been using before:



CREATE OR ALTER PROCEDURE  spSearchAllTablesColumnName (
    @ColumnName SYSNAME
    ,@SearchString NVARCHAR(max)
    )
AS
DECLARE @Tables TABLE (
    TableName SYSNAME
    ,TableSchema SYSNAME
    ,PKColumn NVARCHAR(max)
    )
  
INSERT INTO  @Tables (
    TableName
    ,TableSchema
    ,PKColumn
    )
SELECT Table_name
    ,table_schema
    ,COALESCE(STUFF((
                SELECT N' + ''|'' + ' + ' cast('  + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))'
                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 = C.Table_Schema AND TC.TABLE_NAME = C.Table_Name
 ORDER BY  CU.ORDINAL_POSITION
 FOR XML PATH('')),1,9,''), 'CAST(NULL as nvarchar(max))')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE COLUMN_NAME = @ColumnName
    AND DATA_TYPE IN  (
        'text'
        ,'ntext'
        ,'varchar'
        ,'nvarchar'
        ,'char'
        ,'nchar'
        );
  
IF @@ROWCOUNT = 0
BEGIN
    DECLARE @dbName SYSNAME;
  
    SET @dbName = QUOTENAME(DB_NAME());
  
    RAISERROR (
            'No tables have [%s] column in the database %s'
            ,16
            ,1
            ,@ColumnName
            ,@dbName
            );
  
    RETURN - 1;
END
  
DECLARE @SQL NVARCHAR(max);
  
SELECT @SQL = STUFF((
            SELECT 'UNION ALL
SELECT ' + PKColumn + ' AS PK, ' + quotename(TableSchema, '''') + ' AS TableSchema, '  + quotename(TableName, '''') +
' AS TableName, ' + quotename(@ColumnName) + ' AS ' +
 quotename(@ColumnName) + ' FROM ' + quotename(TableSchema) + '.' + quotename(TableName) +
 ' WHERE ' + quotename(@ColumnName) + ' LIKE ''%' + @SearchString + '%'' '
            FROM @Tables
            ORDER BY  TableSchema
                ,TableName
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 10, '')
  
--PRINT @SQL
  
EXECUTE sp_executeSQL @SQL
    ,N'@SearchString nvarchar(max)'
    ,@SearchString
GO

And we can test this new procedure this way:

EXECUTE spSearchAllTablesColumnName @ColumnName = 'descrip'
    ,@SearchString = N'test';

Let the reader of this article modify this procedure if they want to search for multiple words or multiple words inclusive.

Conclusion

This article examined several common searching scenarios and provided stored procedures that can help in that search. All the code from that article was tested on a local relatively small (but with many tables) database in SQL Server 2012 Developer edition. We would appreciate the readers of this article to perform tests of the shown code and let us know in the comments or inline if there is a problem executing this code.

다음을 통해 공유