In this article, we'll take a look at
Show
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
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'; |
Leave a Comment