By: Aaron Bertrand | Updated: 2022-05-09 | Comments (4) | Related: More > Scripts
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:
- Search all string columns in all SQL Server databases
- Searching and finding a string value in all columns in a SQL Server table
- How to search all columns of all tables in a database for a keyword?
- Validate the contents of large dynamic SQL strings in SQL Server
Collections of related tips and posts:
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-05-09
