SQL Server Search all String Columns in Tables or Views

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</code>; 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.

• Download the script

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
• Validate the contents of large dynamic SQL strings in SQL Server

Collections of related tips and posts:

• Splitting strings in SQL Server
• Using Dynamic SQL safely in SQL Server

Aaron Bertrand

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He also blogs at sqlblog.org.

• MSSQLTips Awards: Author of the Year – 2016, 2023 | Leadership (200+ tips) – 2022