Search all SQL tables for a specific value (useful for reverse engineering to find where data is stored in a database)

search_all_tables.sql

-- this stored proc will generate a list of SELECT statements to show the rows of all tables containing search results.
--  by gojimmypi
CREATE PROCEDURE dbo.proc_SEARCH_ALL_TABLES
  @search_string  as varchar(255),  -- use exact text or SQL wildcards (e.g. '%XYZZY%')
  @min_length     as int = 0,       -- give hints for performance, such as the minimum field size to search, or
  @search_numeric as char(1) = 'N', -- could the data be in a numeric field?
  @search_text    as char(1) = 'Y', -- or perhaps the data could be in a text field?
  @echo_output    as varchar(8) = Null,
  @debug_status   as varchar(8) = Null
  AS
BEGIN
  SET NOCOUNT ON
  
  Declare @ct int
  Declare @res as table(
    table_name  varchar(128),
    field_name  varchar(128)
  )
  Declare @thisCMD as varchar(255)
  Declare @wrk as table(
    wrk_id int identity,
    sqlCMD varchar(255)
   ) 
  
  Declare @target_Database varchar(128); SET @target_Database = DB_NAME()
  
  if @min_length = 0 set @min_length = datalength(@search_string)
  
  /*
  ** first, search for strings
  */
  INSERT INTO @wrk(sqlCMD)
    SELECT  
      'if exists( SELECT 1 FROM ' + @target_Database + '.dbo.' + c.TABLE_NAME + 
      ' WHERE [' + c.COLUMN_NAME + '] like ''' + @search_string + ''') SELECT ''' + c.TABLE_NAME + ''',''' + c.COLUMN_NAME + ''';'
    FROM 
      INFORMATION_SCHEMA.COLUMNS c
    WHERE 
      @search_text = 'Y'
      AND @search_string > ''
      AND c.DATA_TYPE COLLATE DATABASE_DEFAULT in ('varchar','nvarchar','char','nchar')  -- only search strings
      AND c.TABLE_NAME COLLATE DATABASE_DEFAULT in (SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE') -- only search tables, not views
      AND c.CHARACTER_MAXIMUM_LENGTH    > @min_length

  /*
  ** next, search for numbers
  */
  INSERT INTO @wrk(sqlCMD)
    SELECT  
      'if exists( SELECT 1 FROM ' + @target_Database + '.dbo.' + c.TABLE_NAME + 
      ' WHERE [' + c.COLUMN_NAME + '] = ' + @search_string + ') SELECT ''' + c.TABLE_NAME + ''',''' + c.COLUMN_NAME + ''';'
    FROM 
      INFORMATION_SCHEMA.COLUMNS c
    WHERE 
      @search_numeric = 'Y'
      AND @search_string > ''
      AND (IsNumeric(@search_string) > 0)
      AND c.DATA_TYPE COLLATE DATABASE_DEFAULT in ('int','decimal','smallint')  -- only search strings and numbers
      AND c.TABLE_NAME COLLATE DATABASE_DEFAULT in (SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE') -- only search tables, not views
      -- AND IsNull(c.CHARACTER_MAXIMUM_LENGTH,datalength(cast(@search_number as varchar(20))))  >= datalength(cast(@search_number as varchar(20))) -- numbers don't have length & we only search strings longer than the number of digits


  SELECT @ct = max(wrk_id) from @wrk

  /*
  ** -------------------------------------------------------------------------------------------------------------------------------
  ** loop through all data to include all records in email for mass updates
  ** -------------------------------------------------------------------------------------------------------------------------------
  */  
  While @ct > 0 Begin
    SELECT
      @thisCMD = sqlCMD
    FROM
      @wrk
    WHERE
      wrk_id = @ct
    
    If @debug_status = 'Y' print @thiscmd  
    
    INSERT INTO @res(table_name,field_name)
      EXEC(@thisCMD)
      
    SELECT @ct = @ct - 1
  End -- while
  
  /*
  ** return results
  */
  SELECT DISTINCT 
   'SELECT ''' + table_name + ''' as table_name, * FROM ' + table_name + ' WHERE [' + ltrim(rtrim(field_name)) + '] like ''' + @search_string + ''''
  FROM 
    @res
    
    
  RETURN
END
go
-- uncomment to test drive:
-- exec dbo.proc_SEARCH_ALL_TABLES @search_string='%XYZZY%'