Have you ever needed to search for a specific string within all the views and tables in your SQL Server databases? In this article, we will explore a flexible solution that allows you to search for strings in both views and tables, and even limit the search to specific databases.

The Problem

Back in 2015, a tip was published on how to search for strings within string-based columns in all tables across all user databases. However, this solution did not include the ability to search within views or limit the search to specific databases.

The Solution

To address these limitations, a more flexible version of the stored procedure was developed for SQL Server 2016 and above. This new version allows you to search for strings in both views and tables, and specify the databases to search within.

Stored Procedure Signature

The stored procedure, named SearchAllViewsAndOrTables, has the following signature:

CREATE PROCEDURE dbo.SearchAllViewsAndOrTables
  @SearchTerm     nvarchar(255) = NULL,
  @DatabaseList   nvarchar(max) = NULL,
  @SearchTables   bit = 1,
  @SearchViews    bit = 1
AS
BEGIN
  ...
END

The @SearchTerm parameter is used to specify the string to search for. The @DatabaseList parameter allows you to specify a comma-separated list of databases to search within. The @SearchTables and @SearchViews parameters control whether to include tables and views in the search, respectively.

Searching for Strings

The stored procedure uses dynamic SQL to generate the necessary commands to search for the specified string. It first generates the commands for each database:

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)))
          )
        )
      );

Next, it generates 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;';

Finally, the nested dynamic SQL is executed to perform the search:

INSERT #Results
  (
    [database],
    [schema],
    [object],
    [column],
    ArbitraryValue
  )
  EXEC sys.sp_executesql @DatabaseCommands, 
    N'@ColumnCommands nvarchar(max), @SearchTerm nvarchar(255)', 
    @ColumnCommands, @SearchTerm;

Example Usage

Here are some example calls to the SearchAllViewsAndOrTables stored procedure:

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

Conclusion

Searching for specific strings within SQL Server views and tables can be a challenging task. However, with the flexible stored procedure provided in this article, you can easily search for strings in both views and tables, and even limit the search to specific databases. This solution is compatible with SQL Server 2016 and above, making it a valuable tool for any SQL Server developer or administrator.

Article Last Updated: 2022-05-09