Home » Script to search for a string in an entire SQL Server database

Script to search for a string in an entire SQL Server database

by Vlad Drumea

This post contains a script that I’ve written and used a few times when I’ve needed to search for a string in an entire SQL Server database.

You can find more of my scripts by checking out the SQL Server Scripts category of my blog.

What it does

The script can search for a string in SQL Server database by doing the following steps:

  1. Uses the System Information Schema views to look for tables containing string (NVARCHAR, VARCHAR, CHAR, NCHAR, TEXT, and NTEXT) columns.
  2. Builds a WHERE clause based on the columns found in step 1 and the parameter values provided.
  3. Queries the matching record counts from tables identified at step 1 with the WHERE clause built during step 2.
  4. Stores info for tables with at least 1 matching record.
  5. Returns a summary and queries the identified tables to retrieve the relevant records.

Side-note: TEXT and NTEXT are deprecated, so you should look into switching to VARCHAR(MAX) and NVARCHAR(MAX).


I’m listing here only the parameters that can/should be provided with different values:

  • @SearchString – this is the string that you want to look for in all the tables of your database
  • @UseLike – set this to 1 if you’re not expecting an exact match (Column = @SearchString), 0 for partial match (Column LIKE '%'+@SearchString+'%')
  • @IsUnicode – set it to 1 if the string you’ve provided has Unicode characters and the target columns are N(VAR)CHAR and/or NTEXT, if not and you’re database uses mostly (VAR)CHAR and/or TEXT then set it to 0
  • @CaseSensitive – set it to 1 if you’re using a case-sensitive collation and are uncertain about the case of the string in the columns

Note about potential performance impact

The performance of this script depends on a combination of factors:

  • The size of your database
  • Your table design and indexing strategy
  • The data type of your columns
  • The type of search you opt for

So on smaller databases this script performs ok in most situations, but the larger the database is the more you’d want to avoid @UseLike = 1, @CaseSensitive = 1, and make sure to match @IsUnicode to the data type used by the majority of your columns.

Obviously, I can’t stop you from running this with @UseLike = 1 on a 2TB database, but just know that you might have to wait a while for it to finish and it will also potentially block other operations in the process.


You can also find this script in my SQL Server Scripts repo.

Example result set

If any matches are found, the script returns multiple result sets.

The first result set is a summary consisting of the following:

  • the table name(s)
  • number of matching records per table
  • search string
  • WHERE clause
  • the query used to get the data from the identified table(s)

The subsequent result set(s) starts with a column containing the table name followed by the rest of the columns in said table.

The following is a demo result set from AdventureWorks2019 using “elizabeth” as the value for @SearchString, and with @IsUnicode, @UseLike, and @CaseSensitive all set to 1.

And that’s pretty much it. I needed to write variations of this a few times along the years and figured I could share it.

You may also like


Sab December 14, 2023 - 09:08

Very interesting .Thanks for sharing your tricks 🙂

Searching for a String in a SQL Server Database – Curated SQL December 15, 2023 - 15:05

[…] Vlad Drumea is looking for a string: […]


Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.