Procedure for Searching Text in Database Tables - SQL Programmers

  I had come across a requirement to search a string in all columns of all tables of a database with several varchar columns each, looking for various string data. I had to find a stored procedure to search text through all tables. It snakes through the schema of your selected database looking for all chars, varchars, nchars, and nvarchars (Text datatype has been eliminated for performance) looking for my string. It returns the name of the table and column, and the containing text block. I ran it on our staging system and it found my string in about 30 seconds... not too shabby.

The output of this stored procedure contains two columns:

•     The table name and column name in which the search string was found
•     The actual content/value of the column (Only the first 3630 characters are displayed)

Caution

Before you go ahead and run this procedure take a look at the following.

Though this procedure is quite quick on smaller databases, it could take hours to complete on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use the Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirement.

How to run it

Assume that you have to find the text “Wild” in Northwind database

 --To search all columns of all tables in Northwind database for the keyword "Wild"  
    EXEC dbo.prcFindTextInAllTables 'Wild'  
    GO 

Here is the complete stored procedure code:

     
    CREATE PROC  dbo.prcFindTextInAllTables  
        (  
            @SearchStr nvarchar(100)  
        )  
    AS  
    BEGIN  
      
    -- Purpose: To search all columns of all tables for a given search string  
    CREATE TABLE #TestResults (ColName nvarchar(370), ColumnValue nvarchar(3630))  
      
    SET NOCOUNT ON  
      
    DECLARE @TblName nvarchar(256), @ColName nvarchar(128), @SearchStr2 nvarchar(110)  
    SET  @TblName = ''  
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')  
      
    WHILE @TblName IS NOT NULL  
    BEGIN  
        SET @ColName = ''  
        SET @TblName =  
                (  
                    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))  
                    FROM INFORMATION_SCHEMA.TABLES  
                    WHERE TABLE_TYPE = 'BASE TABLE'  
                        AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TblName  
                        AND    OBJECTPROPERTY(  
                                OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  
                                     ), 'IsMSShipped') = 0  
                )  
        WHILE (@TblName IS NOT NULL) AND (@ColName IS NOT NULL)  
    BEGIN  
        SET @ColName =  
        (  
            SELECT MIN(QUOTENAME(COLUMN_NAME))  
            FROM     INFORMATION_SCHEMA.COLUMNS  
            WHERE         TABLE_SCHEMA    = PARSENAME(@TblName, 2)  
                AND    TABLE_NAME    = PARSENAME(@TblName, 1)  
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')  
                AND    QUOTENAME(COLUMN_NAME) > @ColName  
        )  
      
        IF @ColName IS NOT NULL  
        BEGIN  
            INSERT INTO #TestResults  
            EXEC  
            (  
                'SELECT ''' + @TblName + '.' + @ColName + ''', LEFT(' + @ColName + ', 3630)  
                FROM ' + @TblName + ' (NOLOCK) ' +  
                ' WHERE ' + @ColName + ' LIKE ' + @SearchStr2  
            )  
        END  
    END     
    END  
      
    SELECT ColName, ColumnValue FROM #TestResults  
    END