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 a SQL Server database by doing the following steps:
- Uses the System Information Schema views to look for tables containing string (NVARCHAR, VARCHAR, CHAR, NCHAR, TEXT, and NTEXT) columns that are >= to the width of the search string.
- Builds a
WHEREclause based on the columns found in step 1 and the parameter values provided. - Queries the matching record counts from tables identified at step 1 with the WHERE clause built during step 2.
- Stores info for tables with at least 1 matching record.
- 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).
Parameters
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 to1if you’re expecting a partial match (Column LIKE),'%'+@SearchString+'%'0for an exact match (Column LIKE = @SearchString)- @IsUnicode – set it to
1if the string you’ve provided has Unicode characters and (some of) the target columns are N(VAR)CHAR and/or NTEXT, if not and you’re database uses (VAR)CHAR and/or TEXT then set it to0.
When set to 1, the script also matches the input data type with that of the column to avoid performance issues caused by upconverting in case you have a mix of unicode and non-unicde columns. - @CaseSensitive – set it to
1if 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.
As of July 2025, the script also matches the search string data type with the data type of the column.
Updated in August 2025, so that, when @UseLike = 1, the script uses LIKE only if the column is wider than the provided string, otherwise it uses =.
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.
Script
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | /* Script to search for a string in an entire SQL Server database By Vlad Drumea From: https://github.com/VladDBA/SQL-Server-Scripts/ Blog: https://vladdba.com/ More info about this script: https://vladdba.com/2023/12/13/script-to-search-for-a-string-in-an-entire-sql-server-database/ License: https://github.com/VladDBA/SQL-Server-Scripts/blob/main/LICENSE.md */ SET NOCOUNT ON; DECLARE @SearchString NVARCHAR(500)=N'',/*Your string goes here*/ @UseLike BIT = 1,/* set to 1 will do LIKE '%String%', 0 does = 'String'*/ @IsUnicode BIT = 1,/* set to 1 will treat the @SearchString as Unicode in the WHERE clause, set to 0 will treat it as non-Unicode - recommended when dealing with (var)char or text columns the script also matches the input data type with that of the column to avoid performance issues caused by upconverting*/ @CaseSensitive BIT = 0,/*set this to 1 only if you use a case-sensitive collation and are not sure about the case of the string*/ @SQL NVARCHAR(MAX), @TableName NVARCHAR(500), @WhereClause NVARCHAR(MAX), @LineFeed NVARCHAR(5) = CHAR(13) + CHAR(10), @RecordCount INT, @ParamDef NVARCHAR(200); IF OBJECT_ID(N'tempdb..#SearchResults', N'U') IS NOT NULL BEGIN DROP TABLE #SearchResults; END; CREATE TABLE #SearchResults ( [ID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, [TableName] NVARCHAR(500), [RecordsFound] INT, [SearchString] NVARCHAR(500), [WhereClause] NVARCHAR(MAX), [Query] AS N'SELECT * FROM ' + [TableName] + N' WHERE ' + RTRIM([WhereClause]) + N';' PERSISTED ); IF @CaseSensitive = 1 BEGIN SET @SearchString = LOWER(@SearchString); END; DECLARE SearchDB CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR WITH QueryParts AS ( /*Build the table list*/ SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS [QuotedTabName], STUFF( ( /*Build the WHERE clause*/ SELECT N'OR ' + CASE WHEN c.DATA_TYPE LIKE N'%text' THEN N'CAST(' ELSE N'' END + CASE WHEN @CaseSensitive = 1 THEN N'LOWER('+ QUOTENAME(c.COLUMN_NAME) + N')' ELSE QUOTENAME(c.COLUMN_NAME) END + CASE WHEN c.DATA_TYPE = N'ntext' THEN N' AS NVARCHAR(MAX))' WHEN c.DATA_TYPE = N'text' THEN N' AS VARCHAR(MAX))' ELSE N'' END + CASE /*only use LIKE when the column is wider than the string*/ WHEN @UseLike = 1 AND c.CHARACTER_MAXIMUM_LENGTH > LEN(@SearchString) THEN N' LIKE '+ CASE WHEN @IsUnicode = 1 AND c.DATA_TYPE LIKE N'n%' THEN N'N' ELSE N'' END + N'''%' + @SearchString + N'%'' ' ELSE N' = '+ CASE WHEN @IsUnicode = 1 AND c.DATA_TYPE LIKE N'n%' THEN N'N' ELSE N'' END + N'''' + @SearchString + N''' ' END FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME AND (c.DATA_TYPE LIKE N'%char' OR c.DATA_TYPE LIKE N'%text') /*Only search in columns that can actually hold a string the length of the search term*/ AND c.CHARACTER_MAXIMUM_LENGTH >= LEN(@SearchString) FOR XML PATH('') ),1,3,'') AS [WhereClause] FROM INFORMATION_SCHEMA.TABLES AS t WHERE t.TABLE_TYPE='BASE TABLE' ) SELECT [QuotedTabName], [WhereClause] FROM [QueryParts] WHERE [WhereClause] IS NOT NULL OPEN SearchDB; FETCH NEXT FROM SearchDB INTO @TableName, @WhereClause; WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = N'SELECT @RecordCountOut = COUNT(*) FROM ' + @TableName + N' WITH(NOLOCK) WHERE ' + @WhereClause + N';' SET @ParamDef = N'@RecordCountOut INT OUTPUT'; EXECUTE sp_executesql @SQL, @ParamDef, @RecordCountOut= @RecordCount OUTPUT; IF @RecordCount > 0 BEGIN RAISERROR ('%d matching records found in table %s.',10,1,@RecordCount,@TableName) WITH NOWAIT; INSERT INTO #SearchResults ([TableName], [SearchString], [RecordsFound], [WhereClause]) VALUES (@TableName, @SearchString, @RecordCount, @WhereClause); END; FETCH NEXT FROM SearchDB INTO @TableName, @WhereClause; END; CLOSE SearchDB; DEALLOCATE SearchDB; /*Get the summary*/ SELECT [TableName], [RecordsFound], [SearchString], [WhereClause], [Query] FROM #SearchResults ORDER BY [RecordsFound] ASC; /*Query the identified tables to get specific records*/ RAISERROR ('%sRetrieving results from tables.',10,1,@LineFeed) WITH NOWAIT; SET @SQL = CAST(N' '+@LineFeed AS NVARCHAR(MAX)); SELECT @SQL += REPLACE([Query], N'SELECT ', N'SELECT '''+REPLACE(REPLACE([TableName], ']', ''), '[', '')+N''' AS TableName, ') + @LineFeed FROM #SearchResults ORDER BY [RecordsFound] ASC; PRINT @SQL; EXEC(@SQL); DROP TABLE #SearchResults; |
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.
Conclusion
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 in case someone might need to find a string in a database
If you’re interested in more string related posts, check out this post about characters showing up as question marks or this one about NVARCHAR(MAX) strings getting truncated.
4 comments
Very interesting .Thanks for sharing your tricks 🙂
[…] Vlad Drumea is looking for a string: […]
I an trying to use your code to search for a hexadecimal character [char(0x1e)] ~ but it does not work.
I know the character exists:
select charindex(char(0x1e), Description)
from from xxx.JobMtl with(nolock)
works for the same database.
Ant ideas ?
Hi Jerry,
It doesn’t find it because it’s designed for strings to be passed as input, not hex characters.
I would say first try and pass @SearchString on lines 66 and 72 to the CHAR() function and see if that yields any results.
So line 66 would look like this
N”’%’ + CHAR(@SearchString) + N’%” ‘
And line 72 would look like this
END + N”” +CHAR(@SearchString) + N”’ ‘
If that doesn’t work, you’ll need to make more changes and alter the way the WHERE clause is built so that it uses CHARINDEX instead of =, this will also mean that you’ll have to move the CASE from lines 53-57 so that the column name is taken as the second argument of the CHARINDEX function instead of being in front of the LIKE and = operators.