LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon