The Ultimate SQL Database Search Script (That I Forgot to Publish 15 Years Ago)

Wait... I Never Published This?!

Have you ever experienced the "Mandela Effect" with your own blog?

I recently sat down to draft a post about traversing Sitefinity properties via the database. To set up the tutorial, I wanted to include a link back to an essential SQL script that I've used constantly over my career to search across databases.

I started searching my own blog for the link, and... nothing.

As it turns out, even though I have been using this script for over a decade, it has been proudly pinned in my OneNote, and I was certain I had shared it here, apparently I never did. As you'll see from the comments in the script itself, the last modified date is 2011!

Well, the only better time to share this other than 15 years ago is today, so here it is!

A Treasured Relic from the Falafel Days

To understand how deep this cut goes, we have to go back to my days at Falafel Software. One of the other developers on the team passed this script along to me, and it immediately earned a permanent, pinned spot in my OneNote.

While the script has its origins back in the days of SQL Server 2000 and 2005, it is still incredibly effective and relevant today.

I want to give a quick shoutout to the original authors mentioned in the script comments (Narayana Vyas Kondreddi and Tim Gaunt) to honor the rich history this script must have.

Finding the Needle in the Database Haystack

Why is this script so essential? If you have ever worked with massive, normalized databases (especially those behind big CMS platforms like Sitefinity) you know the pain.

You might have a specific value you are tracking down, or need to find a rogue widget property, a random GUID, or a hardcoded string that is somehow causing issues. But looking at the database diagram, you have absolutely no idea which of the hundreds of tables it lives in.

Instead of guessing and writing countless SELECT queries across random tables, you can just ask the database to just search everything.

The "Search Everywhere" Script

Here is the script in all its glory:

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'YOUR_VALUE_HERE'


    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'uniqueidentifier')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

The only line you actually need to change is at the very top, adding the value you're seeking: SET @SearchStr = 'YOUR_VALUE_HERE'

and the script will do the work to locate it!

Under the Hood: How the Magic Happens

If you're curious about how this wizardry actually operates, here is a quick, high-level breakdown.

1. Temporary Storage: The script creates a temporary table (#Results) to hold the matches it finds.
2. Finding the Tables and Columns: It uses INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS to iterate through all user-created tables in the database.
3. Filtering by Data Type: It filters the columns to only include text and GUID data types (char, varchar, nchar, nvarchar, uniqueidentifier) because those are the fields where a string search makes sense.
4. Dynamic SQL Execution: It builds and executes a dynamic SELECT statement (EXEC(...)) for each valid column, looking for the target string (LIKE @SearchStr2), and inserts any matches into the temporary #Results table.
5. Output and Cleanup: Finally, it queries the results table to show you where your string was found, and drops the temporary table.

Up Next: Taming Rogue Sitefinity Properties

It is always satisfying to finally get something out of the drafts (or out of OneNote) and published. Now that this essential tool is officially on the blog, I can finally use it for the post I was originally trying to write!

Stay tuned for the next post, where I'll put this exact script to work tracking down a rogue property in a random Sitefinity widget.