Search Text In All Tables

Posted: February 26, 2009 in SQL Server 2005

To search for text in all database columns that hold CHAR, VARCHAR, NCHAR and NVARCHAR data types for all tables, open a new query window and execute the following line of code:

CREATE PROCEDURE p_SearchForText
                (@iSearchString VARCHAR(100))
AS
BEGIN

    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @lvTableName     VARCHAR(100);
    DECLARE @lvColumnName    VARCHAR(100);
    DECLARE @lvSQL           VARCHAR(2000);
    DECLARE @lvSearchString2 VARCHAR(2000);

    -- Declare temp table
    DECLARE @MyTable TABLE ([ColumnName]  VARCHAR(200)
                           ,[ColumnValue] VARCHAR(2000)
                           );

    -- Initialise variables
    SET @lvTableName     = '';
    SET @lvColumnName    = '';
    SET @lvSQL           = '';
    SET @lvSearchString2 = QUOTENAME('%' + @iSearchString + '%', '''');

    -- Declare cursor to get list of tables in the system
    DECLARE curTableList CURSOR FOR
    SELECT  QUOTENAME([table_schema]) + '.' + QUOTENAME([table_name])
    FROM    [information_schema].[tables]
    WHERE   [table_type] = 'BASE TABLE'
    AND     [table_name] LIKE '%%';

    -- Open cursor to get list of tables to process
    OPEN  curTableList
    FETCH NEXT FROM curTableList
    INTO  @lvTableName;

    -- Loop until all tables are processed
    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Declare cursor to get list of columns for the table
        DECLARE curTableColumnList CURSOR FOR
        SELECT  QUOTENAME([column_name])
        FROM    [information_schema].[columns]
        WHERE   [table_schema]  = PARSENAME(@lvTableName, 2)
        AND     [table_name]    = PARSENAME(@lvTableName, 1)
        AND     [data_type]    IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR');

        -- Open cursor to get list of tables to process
        OPEN  curTableColumnList
        FETCH NEXT FROM curTableColumnList
        INTO  @lvColumnName;

        -- Loop until all tables are processed
        WHILE @@FETCH_STATUS = 0
        BEGIN

            -- Initiliase variables
            SET @lvSQL = '';

            -- Build the SQL statement to insert the table column
            SET @lvSQL = 'SELECT ''' + @lvTableName + '.' + @lvColumnName + ''',
                         ' + @lvColumnName + ' ' +
                         'FROM ' + @lvTableName + ' (NOLOCK) ' + ' ' +
                         'WHERE ' + @lvColumnName + ' LIKE ' + @lvSearchString2;

            -- Insert record into temp table
            INSERT INTO @Mytable
            EXEC (@lvSQL);

            -- Get the next column from the list
            FETCH NEXT FROM curTableColumnList
            INTO  @lvColumnName;

        END;

        -- Close and deallocate the cursor for column list
        CLOSE      curTableColumnList;
        DEALLOCATE curTableColumnList;

        -- Get the next table from the list
        FETCH NEXT FROM curTableList
        INTO  @lvTableName;

    END;

    -- Close and deallocate the cursor for table list
    CLOSE      curTableList;
    DEALLOCATE curTableList;

    -- Select the results
    SELECT [ColumnName]
          ,[ColumnValue]
    FROM   @MyTable;

END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s