Search the whole database for a string in SQL Server

Recently I came across a strange requirement to search the whole database for a specific string in SQL Server. That is, we have to search for a keyword in almost all the columns in all the tables in a database. On googling, I found a similar question in stack overflow which has a wonderful stored procedure by Narayana Vyas to search the database. Though the stored procedure was created for SQL Server 7.0 & 2000, it still works in SQL Server 2019. Anyway, I have done few changes to the stored procedure to reduce the number of while loops and also included text and ntext columns and a few more minor changes to fit my needs.

Here is the modified stored procedure to search the whole database for a string. Be aware that this stored procedure takes around 5 to 7 seconds to run in the SQL Server sample database WideWorldImporters. It may take longer time in bigger databases.

This stored procedure returns a table with two columns, the first column will have the Schema Name + Table Name + Column Name. The second column will have the actual content of the column.

CREATE PROCEDURE mtbSearchDatabase
(
    @SearchStr nvarchar(100)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @SearchResults TABLE(TableAndColumnName nvarchar(512), ColumnValue nvarchar(max));
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(256), @TableAndColumnName nvarchar(512),
        @TableAndColumnName2 nvarchar(512), @SearchStr2 nvarchar(110);
 
    SET @TableAndColumnName = '';
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''');
 
    WHILE @TableAndColumnName IS NOT NULL
    BEGIN
        SELECT TOP 1 @TableAndColumnName = QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)+ '.' + QUOTENAME(COLUMN_NAME),
                @TableName = QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME), 
                @ColumnName = QUOTENAME(COLUMN_NAME)
            FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) 
            WHERE OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                        ), 'IsMSShipped'
                ) = 0
                AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)+ '.' + QUOTENAME(COLUMN_NAME) > @TableAndColumnName
                AND DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')
                ORDER BY QUOTENAME(TABLE_SCHEMA), QUOTENAME(TABLE_NAME), QUOTENAME(COLUMN_NAME);
 
        IF @TableAndColumnName != ISNULL(@TableAndColumnName2, '')
        BEGIN
            SET @TableAndColumnName2 = @TableAndColumnName;
 
            INSERT INTO @SearchResults
            EXEC ('SELECT ''' + @TableAndColumnName + ''', ' + @ColumnName + 
                ' FROM ' + @TableName + ' WITH (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            );
        END
        ELSE
        BEGIN
            BREAK;
        END
    END
 
    SELECT TableAndColumnName, ColumnValue FROM @SearchResults
END
GO

Reference

Article by Narayana Vyas How to search all columns of all tables in a database for a keyword?

Search the whole database for a string in SQL Server

Reference

You may also like...

Leave your thoughts...Cancel reply