m6W1Sgs7

1-- this stored proc will generate a list of SELECT statements to show the rows of all tables containing search results.
2--  by gojimmypi
3CREATE PROCEDURE dbo.proc_SEARCH_ALL_TABLES
4  @search_string  as varchar(255),  -- use exact text or SQL wildcards (e.g. '%XYZZY%')
5  @min_length     as int = 0,       -- give hints for performance, such as the minimum field size to search, or
6  @search_numeric as char(1) = 'N', -- could the data be in a numeric field?
7  @search_text    as char(1) = 'Y', -- or perhaps the data could be in a text field?
8  @echo_output    as varchar(8) = Null,
9  @debug_status   as varchar(8) = Null
10  AS
11BEGIN
12  SET NOCOUNT ON
13  
14  Declare @ct int
15  Declare @res as table(
16    table_name  varchar(128),
17    field_name  varchar(128)
18  )
19  Declare @thisCMD as varchar(255)
20  Declare @wrk as table(
21    wrk_id int identity,
22    sqlCMD varchar(255)
23   ) 
24  
25  Declare @target_Database varchar(128); SET @target_Database = DB_NAME()
26  
27  if @min_length = 0 set @min_length = datalength(@search_string)
28  
29  /*
30  ** first, search for strings
31  */
32  INSERT INTO @wrk(sqlCMD)
33    SELECT  
34      'if exists( SELECT 1 FROM ' + @target_Database + '.dbo.' + c.TABLE_NAME + 
35      ' WHERE [' + c.COLUMN_NAME + '] like ''' + @search_string + ''') SELECT ''' + c.TABLE_NAME + ''',''' + c.COLUMN_NAME + ''';'
36    FROM 
37      INFORMATION_SCHEMA.COLUMNS c
38    WHERE 
39      @search_text = 'Y'
40      AND @search_string > ''
41      AND c.DATA_TYPE COLLATE DATABASE_DEFAULT in ('varchar','nvarchar','char','nchar')  -- only search strings
42      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
43      AND c.CHARACTER_MAXIMUM_LENGTH    > @min_length
44
45  /*
46  ** next, search for numbers
47  */
48  INSERT INTO @wrk(sqlCMD)
49    SELECT  
50      'if exists( SELECT 1 FROM ' + @target_Database + '.dbo.' + c.TABLE_NAME + 
51      ' WHERE [' + c.COLUMN_NAME + '] = ' + @search_string + ') SELECT ''' + c.TABLE_NAME + ''',''' + c.COLUMN_NAME + ''';'
52    FROM 
53      INFORMATION_SCHEMA.COLUMNS c
54    WHERE 
55      @search_numeric = 'Y'
56      AND @search_string > ''
57      AND (IsNumeric(@search_string) > 0)
58      AND c.DATA_TYPE COLLATE DATABASE_DEFAULT in ('int','decimal','smallint')  -- only search strings and numbers
59      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
60      -- 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
61
62
63  SELECT @ct = max(wrk_id) from @wrk
64
65  /*
66  ** -------------------------------------------------------------------------------------------------------------------------------
67  ** loop through all data to include all records in email for mass updates
68  ** -------------------------------------------------------------------------------------------------------------------------------
69  */  
70  While @ct > 0 Begin
71    SELECT
72      @thisCMD = sqlCMD
73    FROM
74      @wrk
75    WHERE
76      wrk_id = @ct
77    
78    If @debug_status = 'Y' print @thiscmd  
79    
80    INSERT INTO @res(table_name,field_name)
81      EXEC(@thisCMD)
82      
83    SELECT @ct = @ct - 1
84  End -- while
85  
86  /*
87  ** return results
88  */
89  SELECT DISTINCT 
90   'SELECT ''' + table_name + ''' as table_name, * FROM ' + table_name + ' WHERE [' + ltrim(rtrim(field_name)) + '] like ''' + @search_string + ''''
91  FROM 
92    @res
93    
94    
95  RETURN
96END
97go
98-- uncomment to test drive:
99-- exec dbo.proc_SEARCH_ALL_TABLES @search_string='%XYZZY%'