Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL Scripts & Samples

Posted Aug 10, 2009

Find every occurrence of a value in a database

By DatabaseJournal.com Staff

>>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
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName  --set the table to check

-- Loop through table cursor
--keep iterating the tables until there are none left
    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

        --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' 
            --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         
            --unwanted column type, do not check as will throw an error
            PRINT @TableName + '.' +@ColumnName + ' not checked because DataType = ' + @ColumnType
        FETCH NEXT FROM ColumnCursor INTO @ColumnName --get the next column
    -- Close Column cursor
    CLOSE ColumnCursor
    DEALLOCATE ColumnCursor

    FETCH NEXT FROM TableCursor INTO @TableName --get the next table

-- Close Table cursor
CLOSE 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

SQL Scripts & Samples Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.