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

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:

Uses the System Information Schema views to look for tables containing string (NVARCHAR, VARCHAR, CHAR, NCHAR, TEXT, and NTEXT) columns.
Builds a WHERE clause 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 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.

Script

/* 
	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;

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.

Cookie	Duration	Description
cookielawinfo-checbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

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

What it does

Parameters

Note about potential performance impact

Script

Example result set

My favorite SSMS tips and tricks

Recreate a default shared folder in QNAP

You may also like

2 comments

Leave a Comment Cancel Reply