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