Search all String Columns in all SQL Server Tables or Views

By:   |   Updated: 2022-05-09   |   Comments (1)   |   Related: More > Scripts


   Free MSSQLTips whitepaper - "Understanding Windows Server Cluster Quorum Options"

Problem

Back in 2015, I wrote a tip called Search all string columns in all SQL Server databases. That tip focused on finding strings within string-based columns in all tables across all user databases. I was recently asked if this could be made more flexible; for example, can it search views as well, and can it search only in a specific database?

Solution

Yes! Since fielding the question, I decided to make a more flexible version of the stored procedure for SQL Server 2016 and above. We're going to follow the same process of getting to the solution: explain the stored procedure signature, the temporary table to temporarily hold the results, and the commands to send to the database(s).

Like before, we are going to build commands that search every string column in all views and/or tables in one or more databases, returning the first 1,000 characters of some arbitrary matching row.

To support the additional functionality, the signature of the stored procedure becomes:

 CREATE PROCEDURE dbo.SearchAllViewsAndOrTables
 @SearchTerm nvarchar(255) = NULL,
 @DatabaseList nvarchar(max) = NULL,
 @SearchTables bit = 1,
 @SearchViews bit = 1
 AS
 BEGIN
 SET NOCOUNT ON;
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
 IF @SearchTerm IS NULL OR @SearchTerm NOT LIKE N'%[^%^_]%'
 BEGIN
 RAISERROR(N'Please enter a valid search term.', 11, 1);
 RETURN;
 END
 
 DECLARE @DatabaseCommands nvarchar(max) = N'', 
 @ColumnCommands nvarchar(max) = N'';
 

The #temp table where we'll hold the results changes only slightly (we rename the column table to the more appropriate object):

 CREATE TABLE #results
 (
 [database] sysname,
 [schema] sysname,
 [object] sysname, 
 [column] sysname,
 ArbitraryValue nvarchar(1000)
 );
 

To generate the commands for each database, we can use the native STRING_SPLIT, which is what makes this solution SQL Server 2016+ (you can adapt it with other solutions if you need to support an older version):

 SELECT @DatabaseCommands = @DatabaseCommands + N'
 EXEC ' + QUOTENAME(d.name) + '.sys.sp_executesql 
 @ColumnCommands, N''@SearchTerm nvarchar(255)'', @SearchTerm;'
 FROM sys.databases AS d
 WHERE database_id > 4 -- non-system databases 
 AND [state] = 0 -- online 
 AND user_access = 0 -- multi-user
 AND -- database list is empty or database is in the list
 (
 (LEN(COALESCE(@DatabaseList,'')) = 0)
 OR
 (
 EXISTS
 (
 SELECT 1 FROM STRING_SPLIT(@DatabaseList, N',') AS f
 WHERE LOWER(d.name) = LOWER(LTRIM(RTRIM(f.value)))
 )
 )
 ); 
 

That will change the list of databases we'll send commands to; if you want to include system databases, remove the database_id filter.

Next, we can generate the commands for each relevant string column in tables, views, or both:

 DECLARE @q char(1) = char(39);
 
 SET @ColumnCommands = N'DECLARE @q nchar(1) = nchar(39),
 @SearchCommands nvarchar(max);
 
 SET @SearchCommands = N''DECLARE @VSearchTerm varchar(255) = @SearchTerm;'';
 
 SELECT @SearchCommands = @SearchCommands + char(13) + char(10) + N''
 SELECT TOP (1)
 [db] = DB_NAME(),
 [schema] = N'' + @q + s.name + @q + '', 
 [table] = N'' + @q + t.name + @q + '',
 [column] = N'' + @q + c.name + @q + '',
 ArbitraryValue = LEFT(CONVERT(nvarchar(max), '' + QUOTENAME(c.name) + ''), 1000) 
 FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
 WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE 
 WHEN c.system_type_id IN (35, 167, 175) THEN ''V'' 
 ELSE SPACE(0) END + ''SearchTerm;'' 
 FROM sys.schemas AS s
 INNER JOIN sys.objects AS t
 ON s.[schema_id] = t.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
 AND c.max_length >= LEN(@SearchTerm)
 AND t.type IN (' + QUOTENAME(LEFT('U', @SearchTables), @q)
 + ',' + QUOTENAME(LEFT('V', @SearchViews), @q) + N');
 
 PRINT @SearchCommands; -- for debugging later
 
 EXEC sys.sp_executesql @SearchCommands, 
 N''@SearchTerm nvarchar(255)'', @SearchTerm;';
 
 PRINT @ColumnCommands; -- for debugging now
 

Finally, we can send this nested dynamic SQL to sys.sp_executesql; it will execute each search command against the specified object types in each database in the list (or in all online user databases):

 INSERT #Results
 (
 [database],
 [schema],
 [object],
 [column],
 ArbitraryValue
 )
 EXEC sys.sp_executesql @DatabaseCommands, 
 N'@ColumnCommands nvarchar(max), @SearchTerm nvarchar(255)', 
 @ColumnCommands, @SearchTerm;
 
 SELECT [Searched for] = @SearchTerm;
 
 SELECT [database],[schema],[object],[column],ArbitraryValue 
 FROM #Results 
 ORDER BY [database],[schema],[object],[column];
 

Putting it all together (download script here):

 CREATE PROCEDURE dbo.SearchAllViewsAndOrTables
 @SearchTerm nvarchar(255) = NULL,
 @DatabaseList nvarchar(128) = NULL,
 @SearchTables bit = 1,
 @SearchViews bit = 1
 AS
 BEGIN
 SET NOCOUNT ON;
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
 
 IF @SearchTerm IS NULL OR @SearchTerm NOT LIKE N'%[^%^_]%'
 BEGIN
 RAISERROR(N'Please enter a valid search term.', 11, 1);
 RETURN;
 END 
 
 CREATE TABLE #results
 (
 [database] sysname,
 [schema] sysname,
 [object] sysname, 
 [column] sysname,
 ArbitraryValue nvarchar(1000)
 );
 
 DECLARE @DatabaseCommands nvarchar(max) = N'', 
 @ColumnCommands nvarchar(max) = N'';
 
 SELECT @DatabaseCommands = @DatabaseCommands + N'
 EXEC ' + QUOTENAME(name) + '.sys.sp_executesql 
 @ColumnCommands, N''@SearchTerm nvarchar(255)'', @SearchTerm;'
 FROM sys.databases AS d
 WHERE database_id > 4 -- non-system databases 
 AND [state] = 0 -- online 
 AND user_access = 0 -- multi-user
 AND -- database list is empty or database is in the list
 (
 (LEN(COALESCE(@DatabaseList,'')) = 0)
 OR
 (
 EXISTS
 (
 SELECT 1 FROM STRING_SPLIT(@DatabaseList, N',') AS f
 WHERE LOWER(d.name) = LOWER(LTRIM(RTRIM(f.value)))
 )
 )
 );
 
 DECLARE @q char(1) = char(39);
 
 SET @ColumnCommands = N'DECLARE @q nchar(1) = nchar(39),
 @SearchCommands nvarchar(max);
 
 SET @SearchCommands = N''DECLARE @VSearchTerm varchar(255) = @SearchTerm;'';
 
 SELECT @SearchCommands = @SearchCommands + char(13) + char(10) + N''
 SELECT TOP (1)
 [db] = DB_NAME(),
 [schema] = N'' + @q + s.name + @q + '', 
 [table] = N'' + @q + t.name + @q + '',
 [column] = N'' + @q + c.name + @q + '',
 ArbitraryValue = LEFT(CONVERT(nvarchar(max), '' + QUOTENAME(c.name) + ''), 1000) 
 FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
 WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE 
 WHEN c.system_type_id IN (35, 167, 175) THEN ''V'' 
 ELSE SPACE(0) END + ''SearchTerm;'' 
 FROM sys.schemas AS s
 INNER JOIN sys.objects AS t
 ON s.[schema_id] = t.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
 AND c.max_length >= LEN(@SearchTerm)
 AND t.type IN (' + QUOTENAME(LEFT('U', @SearchTables), @q)
 + ',' + QUOTENAME(LEFT('V', @SearchViews), @q) + N');
 
 PRINT @SearchCommands; -- for debugging later
 
 EXEC sys.sp_executesql @SearchCommands, 
 N''@SearchTerm nvarchar(255)'', @SearchTerm;';
 
 PRINT @ColumnCommands; -- for debugging now
 
 INSERT #Results
 (
 [database],
 [schema],
 [object],
 [column],
 ArbitraryValue
 )
 EXEC sys.sp_executesql @DatabaseCommands, 
 N'@ColumnCommands nvarchar(max), @SearchTerm nvarchar(255)', 
 @ColumnCommands, @SearchTerm;
 
 SELECT [Searched for] = @SearchTerm;
 
 SELECT [database],[schema],[object],[column],ArbitraryValue 
 FROM #Results 
 ORDER BY [database],[schema],[object],[column];
 END
 GO
 

And some example calls:

 /* search for "foo" in all tables and views in all user databases: */
 EXEC dbo.SearchAllViewsAndOrTables @SearchTerm = N'%foo%';
 
 /* search for "foo" in all tables and views in db1 and db2: */
 EXEC dbo.SearchAllViewsAndOrTables @SearchTerm = N'%foo%', @DatabaseList = N'db1, db2';
 
 /* search for "foo" in only views in db1: */
 EXEC dbo.SearchAllViewsAndOrTables 
 @SearchTerm = N'%foo%', 
 @DatabaseList = N'db1',
 @SearchTables = 0, 
 @SearchViews = 1;
 
Next Steps

Implement this stored procedure in a utility database, and use it whenever you need to search for the existence of a given string pattern.

Also see these tips and other resources:

Collections of related tips and posts:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights








About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips


Article Last Updated: 2022-05-09

Comments For This Article




Monday, May 9, 2022 - 6:31:52 AM - olaBack To Top(90069)
Hello!
Thanks for a great article!

I would like to download the script, but it 401 on me.