SQL: Find text in all columns in all table

When there is too much unknown you sometimes have to use new ways to get more knowledge, we got a task to analyze the effect of changing the computer name on the application suite .

We could iterate with all teams and get area that should be effected but we decided to try and find better first step analysis and for that we realized that the previous Computer name is saved in several main areas:

1. SQL
2. File System
3. Registry

So for each of those we can run script to search for old name and see if we have problematic area (at least as first step).

Registry and files are very simpler and for SQL one of our Architects (Yoel Tzur) created a sample script that will do the job:

   1: set nocount on 

   2: set transaction isolation level read uncommitted 

   3: if object_ID('tempdb..#t') is not null 

   4:       drop table #t

   5: if object_ID('_t') is null 

   6:       begin 

   7:             create table _t(tname sysname, colname sysname,rows_found int)

   8:       end

   9: create table #t (seq int identity primary key ,tname sysname, colname sysname) 

  10: DECLARE

  11:  @rc  int

  12: ,@i int 

  13: ,@max int

  14: ,@string varchar(1024)

  15: ,@tname sysname 

  16: ,@colname sysname 

  17: , @SQLString Nvarchar(4000)

  18:  ,@ParmDefinition Nvarchar(1024)

  19:  

  20:  SET @string = 'VALUE TO SEARCH'

  21:  

  22: insert #t (tname,colname)

  23: select table_name,column_name 

  24: from information_schema.columns

  25: where data_type like '%char%'

  26:  

  27: SELECT @max = max(seq) from #t 

  28: set @i = 0 

  29: while @i < @max 

  30: Begin 

  31:       set @i = @i + 1 

  32:       select @tname=tname,@colname=colname from #t where seq = @i 

  33:       SET @SQLString = N'SELECT @rc = count(*)  FROM  ' +   @tname + ' WHERE '  + @colname + ' = ' + '''' + @string + ''''

  34:         SET @ParmDefinition = N'@rc int OUTPUT'; 

  35:      execute sp_executesql  @SQLString,@ParmDefinition,@rc=@rc  OUTPUT

  36:       INSERT _t(tname,colname,rows_found)

  37:     SELECT @tname,@colname,@rc 

  38: END

  39:  

  40: select * from _t

So enjoy :)