One of the common questions I encounter in the different SQL related forums is "How to search all columns in all tables in the database". My usual answer is to refer people to the Question #2 in this blog The ten most asked SQL Server questions. However, the recent thread in MSDN forum forced me to re-evaluate the solution I knew about and come up with my own solution.
At first, I would like to demonstrate the solution I came up with and then I'll show how we can expand it.
Search for a string value in all columns of a table
-- First declare variables for test
declare @Table_Name sysname, @SearchString nvarchar(max)
-- Set values for variables for test
select @Table_Name = 'Items', @SearchString = N'COST'
declare @Columns nvarchar(max), @Cols 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
order by COLUMN_NAME
for XML PATH('')),1,2,'')
-- 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)) as ' + QUOTENAME(Column_Name)
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE in ('text','ntext','varchar','nvarchar','char','nchar')
and TABLE_NAME = @Table_Name
order by COLUMN_NAME
for XML PATH('')),1,2,'')
-- set select statement using dynamic UNPIVOT
declare @SQL nvarchar(max)
set @SQL = 'select * from (select '+ @Cols + ' from ' + QUOTENAME(@Table_Name) +
' )src UNPIVOT (Column_Value for ColumnName IN (' + @Columns + ')) unpvt
WHERE Column_Value LIKE ''%'' + @SearchString + ''%'''
--print @SQL
execute sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString
The above select statement will select Column Name and Column Data for the specified search string. It will not add a primary key column to the returned result. Also, if we find the string we're searching for in several columns of the same record, this record will be returned multiple times.
I want to expand on this solution.
First, we add a primary key column to this select statement. In the case when the table has only one primary key column, this column is returned. If the table has more than one primary key columns, this select will return the primary key columns concatenated with '|' character as the result. The order of PK columns will be in the order of the name of the columns. Finally, we'll make this as a stored procedure and call it spSearchStringInTable.
Stored procedure to search a value in all columns in the table
CREATE PROCEDURE spSearchStringInTable
(@SearchString NVARCHAR(MAX),
@Table_Schema sysname,
@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
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)) as ' + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
AND TABLE_NAME = @Table_Name
ORDER BY COLUMN_NAME
FOR XML PATH('')),1,2,'')
SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' cast(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA
AND Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name
ORDER BY CU.COLUMN_NAME
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_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
And if we want to call this SP for all tables in the database, we can use this code
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'Cost'
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 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 execute very long on a database with many big tables. You may change it to only check for the existence of the value in the table instead of returning the actual records. The code modifications for this task is trivial and I leave it to the readers of this blog.
There is an alternative version of this script published here Search all columns in all tables You may like that script as well.
Hope you like this topic and see you next time.