How to Search Every Table in a SQL Server Database for a String Value

You can search every table in a SQL Server database for a specific string value using a stored procedure that iterates through all user tables, checks every character-based column, and returns matching rows. This technique is particularly useful for data audits, debugging, and tracking down where specific values are stored across a complex schema.

Here's how to build and use it.

Why Would You Need to Search Every Table in a Database?

Most DBAs encounter this problem eventually. Someone asks where a customer's email address is stored. A developer needs to find every table that references a specific product code. A compliance audit requires you to locate all instances of a particular value before a data migration. The schema documentation is out of date, or simply doesn't exist.

Manually checking dozens or hundreds of tables isn't practical. You need a systematic way to scan the entire database and return results quickly. That's exactly what the stored procedure below does.

This approach is also useful during incident response. If a value has appeared somewhere it shouldn't, a full-database string search lets you identify the source table without guessing.

What the Stored Procedure Does

The procedure works by looping through every user table in the database using INFORMATION_SCHEMA.TABLES. For each table, it then loops through every column with a character-based data type, specifically char, varchar, nchar, and nvarchar. It builds a dynamic SQL query for each column and executes a LIKE search against your input string.

Matching results are collected into a temporary table and returned as a single result set showing the table and column name alongside the matched value.

A few things worth noting before you run it:

  • It only searches character-based columns. Numeric, date, and binary columns are excluded.
  • It uses NOLOCK hints, which means reads are non-blocking but you may occasionally see uncommitted data.
  • On large databases with many tables and columns, this procedure can take a while to complete. Test it during off-peak hours first.
  • The search is case-sensitive or case-insensitive depending on your database collation.

Step 1: Create the Stored Procedure

Run the following script in the target database to create the stored procedure. You only need to do this once.

CREATE PROC DatabaseQuery
(
    @SearchStr nvarchar(100)
)
AS
BEGIN
    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')
                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
END

Once the procedure exists in your database, searching every table for a string is a single line:

EXEC DatabaseQuery 'your-search-string-here'

Replace your-search-string-here with the value you're looking for. For example, to find every table and column containing the email address john.smith@example.com:

EXEC DatabaseQuery 'john.smith@example.com'

The results will show you the fully qualified column name (schema, table, and column) alongside the matched value. If nothing is found, the result set will be empty.

What to Watch Out For

Performance on large databases. This procedure scans every eligible column in every user table. On a database with 200 tables and an average of 10 character columns each, that's 2,000 individual queries. On a production system with millions of rows, this can run for several minutes or longer. Always test on a non-production copy first, or run it during a maintenance window.

The NOLOCK hint. The procedure uses WITH (NOLOCK) to avoid blocking other queries while it runs. This is generally the right trade-off for a search operation, but be aware it can return dirty reads in high-write environments. For compliance or legal purposes where accuracy is critical, consider running the search against a database backup restored to a test environment instead.

Dynamic SQL and permissions. The procedure uses dynamic SQL via EXEC(). The user running the procedure needs SELECT permissions on all tables being searched. In environments with row-level security or restricted schemas, results may be incomplete.

Column value truncation. Matched values are truncated at 3,630 characters in the results. This is sufficient for most use cases, but if you're searching very long text fields and need the full value, you'll need to modify the LEFT() call in the dynamic SQL.

Views and computed columns are excluded. The procedure filters on TABLE_TYPE = 'BASE TABLE', so views are not searched. It also only targets standard character data types, so computed columns or columns using custom types may be missed.

When Should You Use This?

This kind of full-database string search is best suited to:

  • Data audits - confirming where personally identifiable information (PII) is stored before a privacy review or regulatory compliance check
  • Schema discovery - mapping out an unfamiliar database where documentation is absent or outdated
  • Debugging - tracking down where a specific value is being written when the application code isn't obvious
  • Pre-migration validation - finding all references to a value before renaming, replacing, or removing it
  • Incident response - quickly identifying which tables contain a suspicious or unexpected value

It's not a substitute for proper data cataloguing or schema documentation, but it's an extremely practical tool when you need answers fast.

Key Takeaways

  • You can search every table in a SQL Server database for a string by creating a stored procedure that iterates through INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS, then runs dynamic SQL LIKE queries against each character column.
  • The procedure only searches char, varchar, nchar, and nvarchar columns. Numeric and date columns are not included.
  • On large databases, this search can take several minutes. Run it during off-peak hours or against a restored backup to avoid performance impact on production.
  • The NOLOCK hint keeps the search non-blocking but may return uncommitted data in high-write environments.
  • This technique is particularly valuable for data audits, PII discovery, schema mapping, and pre-migration validation.

If you're regularly dealing with undocumented schemas, data quality issues, or compliance requirements, a structured approach to database management makes a significant difference. DBA Services provides SQL Server health checks and managed DBA support to Australian organisations that need reliable, well-documented database environments. Get in touch to find out how we can help.