Search all String Columns in all SQL Server Tables or Views

By:   |   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:

Collections of related tips and posts:




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




get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand 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




Wednesday, June 29, 2022 - 6:33:22 PM - Greg Robidoux Back To Top (90221)
Hi Jason,

glad to hear you got it working.

-Greg

Wednesday, June 29, 2022 - 4:58:54 PM - Jason Back To Top (90220)
Hi Greg,

This was total user error. I must have goofed somewhere between the simple steps of copy all, paste and execute. I tried it a few times before and got the error in my first comment but this last try worked flawlessly. Thank you so much for your great content and scripts. Our SQL community is much better because of what you guys do!

Cheers,
Jason

Wednesday, June 29, 2022 - 3:58:45 PM - Greg Robidoux Back To Top (90219)
Hi Jason,

can you provide any more details? I just tried the code in the article and the download script and had no issues. I talked with Aaron and he tested as well and was not able to recreate the problem.

Thanks
Greg

Wednesday, June 29, 2022 - 11:19:56 AM - Jason Back To Top (90218)
Thank you for sharing this article and script! Unfortunately, attempting to create the procedure without making any edits returns the following error: Msg 207, Level 16, State 1, Procedure SearchAllViewsAndOrTables, Line 86 [Batch Start Line 2] Invalid column name 'database'.


download














get free sql tips
agree to terms