Search a string in every column of every user table in SQL Server
T-SQL script that searches a string in the text and numeric columns of all user tables in a database.
The script below searches a string in every column of every user table of the current database. It returns, for each match, the table and column name and the value, cut to its first 3,630 characters.
Script
Set the text to find in @SearchStr. The % wildcards are added by the script.
DECLARE @SearchStr NVARCHAR(100), @SearchStr2 NVARCHAR(110), @TableName NVARCHAR(256), @ColumnName NVARCHAR(128)
-------------------------------------------------------------------------------------------------------------
--Set searched string here
SET @SearchStr = 'rolback'
-------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#Results','U') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))
SET NOCOUNT ON
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name))
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(c.name))
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE OBJECT_SCHEMA_NAME(object_id) = PARSENAME(@TableName, 2)
AND OBJECT_NAME(object_id) = PARSENAME(@TableName, 1)
AND t.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(c.name) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
Notes
- Only
char,varchar,nchar,nvarchar,intanddecimalcolumns are searched. Other types, such asdateorfloat, are skipped. - Every row of every user table is read with a
LIKEcondition, so the script can run for a long time on large databases. Run it on a copy of the database or outside production hours. - The matches are stored in the temporary table
#Results, and each value is cut to 3,630 characters.