TechAid24

Your Technical Consultant

How to Search Across All Columns with Multiple Keywords (Complete Guide)

Posted on by Selim Ahmed 7 minutes read

How to Search Across All Columns with Multiple Keywords (Complete Guide)

Searching data efficiently is one of the most common challenges developers face—especially when users want to search using multiple keywords across multiple columns. Whether you’re building a web app, an admin dashboard, or a reporting system, implementing a flexible search can dramatically improve user experience.

In SQL Server, this can quickly turn into a performance nightmare if not handled correctly. In this guide, we’ll explore the three most effective ways to tackle multi-keyword, multi-column searching.

 

Search All Columns with Multiple Keywords
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]

 

Search All Columns in a Table for all Passed Keywords
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');

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

execute dbo.spSearchWordsInTableInclusive @SearchWords = @SWords, @table_schema = 'dbo', @table_name = 'items'

 

Searching Specific Columns in all Tables of a Database

Finally, let’s consider the problem mentioned at 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:

IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'spSearchAllTablesColumnName'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.spSearchAllTablesColumnName AS SET NOCOUNT ON;');
GO
  
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';

Tags:

Selim Ahmed

Thank you for visiting my personal blog. I’m Selim Ahmed, a tech-savvy who loves to learn a new technology every day. Innovation, research, and development is my daily activities. I love to work in a place where I can learn, innovate, and develop new things.Please feel free to reach me on any comments and feedback you have. I would be more than glad to listen and reply.

Leave a Reply

Your email address will not be published. Required fields are marked *