How to Search for a String in All Tables in SQL Server

Somebody hands you a database you have never seen and a value: an email address, a reference code, a customer name. Where is it stored? There is no Ctrl+F for a schema, so the job is to build one.

This is the script I use. It walks every text column in the current database, counts the matches, and shows you a sample of each hit so you can tell a real match from a coincidence. It is read only, and it was verified on SQL Server 2025 against a database seeded specifically to test it.

The safety note comes first, because this script does what it says.

---

Read This Before Running It On Production

The search uses LIKE '%value%'. A leading wildcard cannot use an index, so every row of every text column gets read. On a small database that is instant. On a large one it is a full scan of most of your data, and on a busy production instance it will show up as IO pressure and may block or be blocked.

Three ways to keep it sensible:

• Run it against a restored copy or a non-production environment where you can.
• If it has to be production, scope it to one schema using the parameter below, and run it out of hours.
• Consider adding WITH (NOLOCK) only if you understand what dirty reads mean for your situation. It is not in the script by default, deliberately.

Finding where data lives is a legitimate task. Doing it by scanning a production OLTP database at 10am is not.

---

The Script

/*  Search every text column in the current database for a value.
    Read only. Scans all rows of every matching column.               */

SET NOCOUNT ON;

DECLARE @Search     nvarchar(200) = N'ada';   -- the value to look for
DECLARE @SchemaOnly sysname       = NULL;     -- NULL = all schemas
DECLARE @MatchCase  bit           = 0;        -- 1 = case sensitive

DROP TABLE IF EXISTS #Hits;
CREATE TABLE #Hits (
    SchemaName  sysname,
    TableName   sysname,
    ColumnName  sysname,
    Matches     int,
    SampleValue nvarchar(200)
);

DECLARE @pattern nvarchar(210) = N'%' + @Search + N'%';
DECLARE @collate nvarchar(60)  =
        CASE WHEN @MatchCase = 1 THEN N' COLLATE Latin1_General_CS_AS' ELSE N'' END;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql = @sql + N'
INSERT #Hits
SELECT ' + QUOTENAME(s.name, '''') + N', ' + QUOTENAME(t.name, '''') + N', '
        + QUOTENAME(c.name, '''') + N',
       COUNT(*),
       LEFT(MAX(CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N')), 200)
FROM '  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'
WHERE CONVERT(nvarchar(max), ' + QUOTENAME(c.name) + N')' + @collate + N' LIKE @p
HAVING COUNT(*) > 0;'
FROM sys.columns  c
JOIN sys.tables   t  ON t.object_id     = c.object_id
JOIN sys.schemas  s  ON s.schema_id     = t.schema_id
JOIN sys.types    ty ON ty.user_type_id = c.user_type_id
WHERE ty.name IN ('char','varchar','nchar','nvarchar','text','ntext','sysname')
  AND t.is_ms_shipped = 0
  AND t.temporal_type <> 1            -- skip history tables
  AND (@SchemaOnly IS NULL OR s.name = @SchemaOnly);

IF @sql = N''
    SELECT 'No text columns found to search.' AS Result;
ELSE
BEGIN
    EXEC sp_executesql @sql, N'@p nvarchar(210)', @p = @pattern;

    SELECT SchemaName, TableName, ColumnName, Matches, SampleValue
    FROM #Hits
    ORDER BY Matches DESC, SchemaName, TableName, ColumnName;
END

Set @Search and run it. Nothing else is required.

---

What The Output Looks Like

Against a test database with a Customers, an Orders and a Notes table, searching for ada:

The sample column is what makes this usable. ORD-ada-001 is a substring coincidence, not a customer record, and you can see that at a glance instead of going and looking.

---

How It Works

The script builds one INSERT statement per candidate column, concatenates them all into a single batch, and executes that batch once.

Three pieces are doing the real work.

QUOTENAME on every identifier. Table and column names can contain spaces, reserved words, or brackets. QUOTENAME(c.name) wraps the name safely, and the two-argument form QUOTENAME(s.name, '''') produces a properly escaped string literal for the output columns. Building dynamic SQL by pasting raw names together is how injection gets in, and how a table called Order breaks your script.

The search value is a parameter, not concatenated. It goes in through sp_executesql as @p. A search value containing a quote will not break the batch or execute anything, which matters because the value often comes from somewhere you do not control.

CONVERT(nvarchar(max), ...) around each column. This handles the deprecated text and ntext types, which cannot be compared with LIKE directly. In the test above, the Legacy column is a text column and it was searched successfully because of that conversion.

Two filters keep the results clean: t.is_ms_shipped = 0 skips Microsoft’s own objects, and t.temporal_type <> 1 skips system-versioned history tables, which would otherwise double every hit on a temporal table.

---

Case Sensitivity

By default this is case insensitive, because the common SQL Server collation SQL_Latin1_General_CP1_CI_AS is. The CI is case insensitive.

Set @MatchCase = 1 and the script appends COLLATE Latin1_General_CS_AS to each comparison. Running the same search that way against the test data drops Ada Lovelace from the results and returns four rows instead of five, because the capital A no longer matches a lower case search.

Useful when you are looking for a case-specific code and the noise is drowning the signal.

---

Scoping It Down

@SchemaOnly limits the search to a single schema:

DECLARE @SchemaOnly sysname = N'sales';

On a large database this is the difference between a query you can run and one you cannot. If you know roughly where the data lives, use it.

To go further, narrow the column list in the WHERE clause. Dropping text and ntext from the type list, or excluding nvarchar(max) columns that hold large documents, cuts the work substantially.

---

What It Does Not Search

Worth being explicit, because a clean empty result is not proof the value is absent:

• Numeric, date and binary columns. A reference stored as an int will not be found. Cast the search value and query those columns directly.
• XML and JSON columns. XML is a separate type and needs .exist() or .value(). JSON stored in an nvarchar column is searched, since as far as SQL Server is concerned that is just text.
• Column and object names. This searches data, not metadata. To find a column called something, query sys.columns directly.
• Other databases. It runs against the current database only. Change database and run it again.

---

Finding It In Object Definitions Instead

A related question that gets asked in the same breath: where is this string used in code, rather than stored in data? That is a different query and a much cheaper one:

SELECT o.type_desc, SCHEMA_NAME(o.schema_id) AS SchemaName, o.name
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%SearchTerm%'
ORDER BY o.type_desc, o.name;

That covers stored procedures, views, functions and triggers. It is the one to reach for when you are tracing where a hardcoded value or a table reference appears in code.

---

Frequently Asked Questions

---

Related

• View the Definition of a Stored Procedure, searching the code rather than the data
• DBA Scripts: Get Database Inventory
• DBA Scripts: Get Table Sizes
• DBA Scripts: Get Schema Change History
• DBA Scripts: The Complete Guide

---

Summary

One script, set @Search, run it, and you get every text column in the database that contains the value along with a count and a sample of each. It uses QUOTENAME on identifiers and a real parameter for the search value, handles the deprecated text and ntext types, skips system and temporal history tables, and has a case-sensitive mode when the noise gets too much.

The one thing to keep in mind is cost. This is a full scan by design, so scope it with @SchemaOnly or point it at a restored copy before you point it at anything that matters.