Find every occurrence of a value in a database

>>Script Language and Platform: SQL Server
Script will iterate every field within a database for every occurrence of a specified value.
Takes a single string input and does a LIKE search on every field in the database.
Outputs a data set for each table and column combination where the specified field value is found. A row of data is reported for each field value returned. The column header of the returned data set is formatted to report the table name separated with an underscore from the column name.

The script uses a cursor to iterate the tables schema. It then uses a nested cursor to iterate the columns schema. It avoids data types that may throw an error.

Have tested it on a database with 8835 columns, 119560 rows, 2648822 fields of genuine data. Have a 2.13Gb dual core and it took 32secs.

Author: Phil Styler

–Find every occurence of a specified field value within a database by

—  iterating every table and column.

–NB: the data set header returned, is formatted to report the

— table name separated by an underscore from the column name.

–Written by Phil Styler (from New Zealand), Sept 2008.

DECLARE @TableName nvarchar(250)

DECLARE @ColumnName nvarchar(250)

DECLARE @ColumnType nvarchar(250)

DECLARE @TextToFind nvarchar(250)

DECLARE @ReturnValue nvarchar(max)

SET @TextToFind = ‘Test’  –Set the text to search for
–open the tables cursor

DECLARE TableCursor CURSOR FOR SELECT [NAME] FROM sys.tables ORDER BY [NAME]

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName  –set the table to check
— Loop through table cursor

WHILE @@FETCH_STATUS = 0

–keep iterating the tables until there are none left

BEGIN
    DECLARE ColumnCursor CURSOR FOR SELECT column_name

        FROM information_schema.columns WHERE table_name = @TableName
    –open the columns cursor

    OPEN ColumnCursor

    FETCH NEXT FROM ColumnCursor INTO @ColumnName  
    — Loop through the columns cursor

    WHILE @@FETCH_STATUS = 0 –keep iterating the columns until there are none left

    BEGIN
        –initialise the return variable

        SET @ReturnValue = ”
        –get the columns data type

        SET @ColumnType = (SELECT Data_Type FROM information_schema.columns

            WHERE table_name = @TableName AND column_name = @ColumnName)

        –PRINT @TableName + ‘.’ +@ColumnName + ‘ DataType = ‘ + @ColumnType
        –check all columns types except: “image”

        IF @ColumnType != ‘image’

        BEGIN

            –search the column within the current table for the search text

            SET @ReturnValue = ‘IF EXISTS(SELECT [’ + @ColumnName + ‘] AS ‘ + @TableName + ‘_’ + @ColumnName +

                            ‘ FROM [’ + @TableName + ‘]

                            WHERE [’ + @ColumnName + ‘] LIKE ”%’ + @TextToFind + ‘%”)

                            SELECT [’ + @ColumnName + ‘] AS ‘ + @TableName + ‘_’ + @ColumnName +

                            ‘ FROM [’ + @TableName + ‘]

                            WHERE [’ + @ColumnName + ‘] LIKE ”%’ + @TextToFind + ‘%”’

            EXECUTE sp_EXECUTESQL @ReturnValue

            –PRINT @TableName + ‘.’ +@ColumnName

            –PRINT ‘@ReturnValue = ‘ + @ReturnValue

        END

        ELSE

        BEGIN

            –unwanted column type, do not check as will throw an error

            PRINT @TableName + ‘.’ +@ColumnName + ‘ not checked because DataType = ‘ + @ColumnType

        END        
        FETCH NEXT FROM ColumnCursor INTO @ColumnName –get the next column

    END

    — Close Column cursor

    CLOSE ColumnCursor

    DEALLOCATE ColumnCursor
    FETCH NEXT FROM TableCursor INTO @TableName –get the next table

END 
— Close Table cursor

CLOSE TableCursor

DEALLOCATE TableCursor

Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home