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 link back to this essential SQL script that I use constantly to search across databases. I started searching my own blog for the link, and... crickets. Nothing.

Insert Confused John Travolta (Pulp Fiction) meme here: Me looking for the blog post I swore I wrote in 2011.

As it turns out, 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. But I didn't. As you'll see from the comments in the script itself, the last modified date is 2011!

Well, it’s been 84 years, but it's finally time to share the wealth.

A Treasured Relic from the Falafel Days

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

Insert Star Wars "It's an older code, sir, but it checks out" meme here.

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 has on the web.

Finding the Needle in the Database Haystack

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

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

Insert Charlie Day (It's Always Sunny) Conspiracy Board meme here: Trying to manually trace relationships in a CMS database.

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

The "Search Everywhere" Script

The beauty of this script is its simplicity. The only line you actually need to change is at the very top: SET @SearchStr = 'YOUR_VALUE_HERE'.

Insert Liam Neeson (Taken) meme here: "I don't know which table you're in... but I will look for you, I will find you."

Here is the script in all its glory:

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '66F78FFF-6843-6BBE-BB36-FF00005543C0'


    -- 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

Under the Hood: How the Magic Happens

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

Insert Hackerman (Kung Fury) meme here: "Writing dynamic SQL loops."

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 into the wild. Now that this essential tool is officially on the blog, I can finally use it for the post I was originally trying to write!

Insert "To Be Continued" Arrow (JoJo's Bizarre Adventure) meme here.

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.