Search Whole Database SQL 2008

******************************************************************************/

DECLARE @searchFor VARCHAR(255)        SET @searchFor = '%AW999000%'

DECLARE @dbMask VARCHAR(255)           SET @dbMask = 'CGLHRIS'
DECLARE @includeUnderscoreTables BIT   SET @includeUnderscoreTables = 0

DECLARE @debug BIT                     SET @debug = 0

/*****************************************************************************/

SET NOCOUNT ON

-- Table that holds the list of tables to ignore
IF OBJECT_ID('tempdb..#ignore') IS NOT NULL DROP TABLE #ignore
CREATE TABLE #ignore (
    [TABLE_NAME] VARCHAR(255) PRIMARY KEY
    )

-- Populate ignore table
-- INSERT #ignore ([TABLE_NAME])

-- Results table
IF object_Id('tempDb..#found') IS NOT NULL DROP TABLE #found
CREATE TABLE #found (
    [databaseName] NVARCHAR(255)
    , [tableName] NVARCHAR(255)
    , [columnName] NVARCHAR(255)
    , [Id] NVARCHAR(255)
    , [value] NVARCHAR(MAX)
    )

-- SeachList
IF object_Id('tempDb..#searchList') IS NOT NULL DROP TABLE #searchList
CREATE TABLE #searchList (
    [TABLE_CATALOG] NVARCHAR(255)
    , [TABLE_SCHEMA] NVARCHAR(255)
    , [TABLE_NAME] NVARCHAR(255)
    , [COLUMN_NAME] NVARCHAR(255)
    , [COLLATION_NAME] NVARCHAR(255)
    , [DATA_TYPE] NVARCHAR(255)
    , [ID_FIELD] BIT DEFAULT 0
    )


-- Variables
DECLARE @sql NVARCHAR(MAX)
DECLARE @database VARCHAR(255)
DECLARE @tableName VARCHAR(255)
DECLARE @columnName VARCHAR(255)
DECLARE @schema VARCHAR(255)
DECLARE @dataType VARCHAR(255)
DECLARE @collation VARCHAR(255)
DECLARE @idField BIT
DECLARE @msgText VARCHAR(300)
DECLARE @now DATETIME


-- Populate the SearchList
DECLARE databaseCursor CURSOR LOCAL READ_ONLY FOR SELECT
    [name]
FROM
    sys.databases
WHERE
    [name] LIKE @dbMask

OPEN databaseCursor

    FETCH NEXT FROM databaseCursor INTO @database

    WHILE ( @@FETCH_STATUS = 0 ) BEGIN

        SET @msgText = 'Scanning ' + QUOTENAME(@database) + ' for target columns'
        RAISERROR(@msgText, 0, 1) WITH NOWAIT

        SET @sql = N'
        INSERT INTO #searchList (
            [TABLE_CATALOG]
            , [TABLE_SCHEMA]
            , [TABLE_NAME]
            , [COLUMN_NAME]
            , [COLLATION_NAME]
            , [DATA_TYPE]
            )
        SELECT
            QUOTENAME([TABLE_CATALOG])
            , QUOTENAME([TABLE_SCHEMA])
            , QUOTENAME([TABLE_NAME])
            , QUOTENAME([COLUMN_NAME])
            , [COLLATION_NAME]
            , [DATA_TYPE]
        FROM
            ' + QUOTENAME(@database) + '.information_schema.columns ic
        WHERE
            ic.[DATA_TYPE] IN (
                ''VARCHAR''
                , ''TEXT''
                , ''CHAR''
                , ''NVARCHAR''
                , ''NCHAR''
                , ''NTEXT''
            )

            AND (
                ic.[TABLE_NAME] NOT LIKE ''[_]%''
                OR @includeUnderscoreTables = 1
                )

            AND NOT EXISTS (
                SELECT 1
                FROM
                    #ignore i
                WHERE
                    i.[TABLE_NAME] = ic.[TABLE_NAME] COLLATE DATABASE_DEFAULT
                )

        UPDATE sl
        SET
            [ID_FIELD] = 1
        FROM
            #searchList sl
            JOIN ' + QUOTENAME(@database) + '.information_schema.columns isc ON
                QUOTENAME(isc.[TABLE_CATALOG]) = sl.[TABLE_CATALOG] COLLATE DATABASE_DEFAULT
                AND QUOTENAME(isc.[TABLE_SCHEMA]) = sl.[TABLE_SCHEMA] COLLATE DATABASE_DEFAULT
                AND QUOTENAME(isc.[TABLE_NAME]) = sl.[TABLE_NAME] COLLATE DATABASE_DEFAULT
                AND isc.[COLUMN_NAME] = ''Id'' COLLATE DATABASE_DEFAULT'

        IF @debug = 1 PRINT @sql

        EXEC sp_ExecuteSql
            @sql
            , N'@includeUnderscoreTables BIT
            , @database VARCHAR(255)'
            , @includeUNderscoreTables
            , @database

        FETCH NEXT FROM databaseCursor INTO @database
    END
CLOSE databaseCursor
DEALLOCATE databaseCursor

RAISERROR('', 0, 1) WITH NOWAIT

SET @msgText = 'Starting Search for string : ' + REPLACE(@searchFor, '%', '*')
RAISERROR(@msgText, 0, 1) WITH NOWAIT

RAISERROR('', 0, 1) WITH NOWAIT

-- Now serach through the list. This can take a while!
DECLARE searchCursor CURSOR LOCAL READ_ONLY FOR
SELECT
    [TABLE_CATALOG]
    , [TABLE_SCHEMA]
    , [TABLE_NAME]
    , [COLUMN_NAME]
    , [DATA_TYPE]
    , [COLLATION_NAME]
    , [ID_FIELD]
FROM
    #searchList
ORDER BY
    [TABLE_CATALOG]
    , [TABLE_SCHEMA]
    , [TABLE_NAME]
    , [COLUMN_NAME]

OPEN searchCursor

    FETCH NEXT FROM searchCursor INTO
        @database
        , @schema
        , @tableName
        , @columnName
        , @dataType
        , @collation
        , @IdField

    WHILE ( @@FETCH_STATUS = 0 ) BEGIN

        SET @now = GETDATE()
        SET @msgText =
            CONVERT(CHAR(11), @now, 106) + ' @ '
            + CONVERT(CHAR(8), @now, 108)
            + ' - Checking.... '
            + @database +
            '.'
            + @schema
            +
            '.'
            + @tableName
            + '.'
            + @columnName

        RAISERROR(@msgText, 0 , 1) WITH NOWAIT
           
        SET @sql = N'
        INSERT INTO #found (
            [databaseName]
            , [tableName]
            , [columnName]
            , [Id]
            , [value]
            )
        SELECT
            @database
            , @tableName
            , @columnName
            , ' + CASE @IdField WHEN 1 THEN '[Id]' ELSE 'NULL' END + '
            , ' + @columnName + '
        FROM
            ' + @database + '.' + @schema + '.' + @tableName + ' WITH (NOLOCK)
        WHERE
            ' + @columnName + ' LIKE @searchFor'

        -- Show and execute the sql
        IF @debug = 1 PRINT @sql

        EXEC sp_executeSql
            @sql
            , N'@database NVARCHAR(255)
            , @tableName NVARCHAR(255)
            , @columnName NVARCHAR(255)
            , @searchFor NVARCHAR(255)'
            , @database
            , @tableName
            , @columnName
            , @searchFor

        FETCH NEXT FROM searchCursor INTO
            @database
            , @schema
            , @tableName
            , @columnName
            , @dataType
            , @collation
            , @IdField

    END

CLOSE searchCursor
DEALLOCATE searchCursor

SELECT * FROM #found