SQL Server - Search for String in All Tables of Database

Submitted by Mike Rodey on March 25, 2012 - 9:27am

In the article Oracle PL/SQL - Search for String in All Tables of Schema I promised to show how to search for a string in all tables of a SQL Server database using T-SQL. Replacing the @SearchStr variable in the script below will perform a case-insensitive search for the string in all tables not shipped with SQL Server in the current database. The script searches every column and will report the exact column where a match is found, but this could run quite slowly if you have many large tables. If the script is too slow for you it could be modified to search once for each table and report if a match was found in the table without narrowing it down to a specific column. I used this strategy with the Oracle PL/SQL script in the article referenced above. Obviously you can use the USE statement at the top of the script to force it to run in a different database.

DECLARE @SearchStr nvarchar(100)

SET @SearchStr = 'FIND_ME'

CREATE TABLE #Results(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + LOWER(@SearchStr) + '%', '''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                    ), 'IsMSShipped') = 0)

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName = (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
                AND	TABLE_NAME	= PARSENAME(@TableName, 1)
                AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND	QUOTENAME(COLUMN_NAME) > @ColumnName)

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC(
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' WITH (NOLOCK) ' +
                ' WHERE LOWER(' + @ColumnName + ') LIKE ' + @SearchStr2)
        END
    END	
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

Tags: