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.
- 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 not expecting an exact match (Column = @SearchString),0for partial match (Column LIKE '%'+@SearchString+'%')- @IsUnicode – set it to
1if 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 to0 - @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.
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 | /* 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/ License https://github.com/VladDBA/SQL-Server-Scripts/blob/main/LICENSE.md */ SET NOCOUNT ON; DECLARE @SearchString NVARCHAR(500)=N'SomeString',/*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*/ @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 @CaseSensitive = 1 THEN N'LOWER('+ QUOTENAME(c.COLUMN_NAME) + N')' ELSE QUOTENAME(c.COLUMN_NAME) END + CASE WHEN @UseLike = 1 THEN N' LIKE '+ CASE WHEN @IsUnicode = 1 THEN N'N' ELSE N'' END + N'''%' + @SearchString + N'%'' ' ELSE N' = '+ CASE WHEN @IsUnicode = 1 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 (DATA_TYPE LIKE N'%char' OR DATA_TYPE LIKE N'%text') 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; PRINT CAST(@RecordCount AS NVARCHAR(10)) + ' records found in table ' + @TableName IF @RecordCount > 0 BEGIN 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*/ PRINT @LineFeed + 'Retrieving results from tables'; SET @SQL = N''; 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.
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
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.