Find a value anywhere (Views) in a database

Question

I would like to search through an MS SQL Server DB for a specific value in the SQL Views.

This SO post is helpful to search all tables for a specific value (Find a value anywhere in a database) but does not cover Views.

Does anyone have any SQL Script they can share? Google has only turned up how to search through all the tables for the value.

Answer 1

This procedure (based on this tip from 2015) will build a separate search command for every string column in any view, table, or both, in any database, or all user databases. Note that I use sys.objects instead of INFORMATION_SCHEMA for reasons I outline here.

CREATE OR ALTER PROCEDURE dbo.SearchViewsAndOrTables
  @SearchTerm     nvarchar(255) = NULL,
  @SingleDatabase nvarchar(128) = NULL,
  @ViewsOnly      bit = 0,
  @TablesOnly     bit = 0
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,
    ExampleValue nvarchar(4000)
  );

  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 
    WHERE database_id  > 4  -- non-system databases  
      AND [state]      = 0  -- online 
      AND user_access  = 0  -- multi-user
      AND LOWER(name)  = LOWER(COALESCE(@SingleDatabase, name));
 
    SET @ColumnCommands = N'DECLARE @q nchar(1),
          @SearchCommands nvarchar(max);
    
    SELECT @q = nchar(39), 
      @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 + '',
        ExampleValue = LEFT('' + QUOTENAME(c.name) + '', 4000) 
      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 '''' 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 (' 
        + CASE @TablesOnly WHEN 1 THEN '''''' ELSE '''V''' END
        + ',' 
        + CASE @ViewsOnly  WHEN 1 THEN '''''' ELSE '''U''' END
        + N');

    PRINT @SearchCommands;
    EXEC sys.sp_executesql @SearchCommands, 
      N''@SearchTerm nvarchar(255)'', @SearchTerm;';

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

  SELECT [Searched for] = @SearchTerm;
  
  SELECT [database],[schema],[object],[column],ExampleValue 
    FROM #Results 
    ORDER BY [database],[schema],[object],[column];
END
GO
Copy