Various search queries for MS SQL server

search.sql

alter proc search (@query nvarchar(100))
/*
** Purpose:	Search for a specific value in all columns in all tables
** Notes:	
set statistics time on
exec q_sys_search 'q_'
set statistics time off
** Revision History:
** yyyy-mm-dd	Author					    Comments
** ----------	--------------			--------------------
** 2012-01-11	Mikael Henriksson	  	Initial version
*/
as 
begin
      
      create table #Results (ColumnName nvarchar(370)
                           , ColumnValue nvarchar(3630))

      set NOCOUNT on

      declare @tablename nvarchar(256)
            , @columnname nvarchar(128)
            , @query2 nvarchar(110)
      set @tablename=''
      set @query2=quotename('%'+@query+'%', '''')

      while @tablename is not null 
            begin
                  set @columnname=''
                  set @tablename=(select  min(quotename(TABLE_SCHEMA)+'.'+quotename(TABLE_NAME))
                                  from    INFORMATION_SCHEMA.TABLES
                                  where   TABLE_TYPE='BASE TABLE'
                                          and quotename(TABLE_SCHEMA)+'.'+quotename(TABLE_NAME)>@tablename
                                          and objectproperty(object_id(quotename(TABLE_SCHEMA)+'.'+quotename(TABLE_NAME)), 'IsMSShipped')=0)

                  while (@tablename is not null)
                        and (@columnname is not null) 
                        begin
                              set @columnname=(select min(quotename(COLUMN_NAME))
                                               from   INFORMATION_SCHEMA.COLUMNS
                                               where  TABLE_SCHEMA=parsename(@tablename, 2)
                                                      and TABLE_NAME=parsename(@tablename, 1)
                                                      and DATA_TYPE in ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                                                      and quotename(COLUMN_NAME)>@columnname)
    
                              if @columnname is not null 
                                 begin
                                       insert into #Results
                                              exec ('SELECT '''+@tablename+'.'+@columnname+''', LEFT('+@columnname+', 3630) FROM '+@tablename+' (NOLOCK) '+' WHERE '+@columnname+' LIKE '+@query2)
                                 end
                        end    
            end

      select  ColumnName, ColumnValue
      from    #Results
      drop table #Results
end

string_search.sql

set QUOTED_IDENTIFIER on
set ANSI_NULLS on
GO
create procedure string_search
/*
** Purpose:	Search for a specific value in all columns in all tables
** Notes:	
**    exec q_sys_stringsearch 'q_', 0
** Revision History:
** yyyy-mm-dd	Author					    Comments
** ----------	--------------			--------------------
** 2012-01-11	Mikael Henriksson	  	Initial version
*/     @query nvarchar(4000)
     , @exact_match bit=0
as 
set NOCOUNT on
 
declare @tmp table (row_id int identity(1, 1)
                  , schemaname sysname
                  , tablename sysname
                  , columnname sysname
                  , datatype varchar(100)
                  , datafound bit)
 
insert  into @tmp (tablename, schemaname, columnname, datatype)
        select  c.Table_Name, c.TABLE_SCHEMA, c.Column_Name, c.Data_Type
        from    Information_Schema.Columns as c
                inner join Information_Schema.Tables as t on c.Table_Name=t.Table_Name
                                                             and c.TABLE_SCHEMA=t.TABLE_SCHEMA
        where   Table_Type='Base Table'
                and Data_Type in ('ntext', 'text', 'nvarchar', 'nchar', 'varchar', 'char')
 
 
declare @i int
declare @max int
declare @table sysname
declare @column sysname
declare @schema sysname
declare @sql nvarchar(4000)
declare @params nvarchar(4000)
declare @data_exists bit
declare @template nvarchar(4000)
 
select  @template=case when @exact_match=1 then 'If Exists(Select *
                                          From   Replacetablename
                                          Where  Convert(nVarChar(4000), [Replacecolumnname])
                                                       = '''+@query+'''
                                          )
                                     Set @data_exists = 1
                                 Else
                                     Set @data_exists = 0'
                       else 'If Exists(Select *
                                          From   Replacetablename
                                          Where  Convert(nVarChar(4000), [Replacecolumnname])
                                                       Like ''%'+@query+'%''
                                          )
                                     Set @data_exists = 1
                                 Else
                                     Set @data_exists = 0'
                  end, @params='@data_exists Bit OUTPUT', @i=1
 
select  @i=1, @max=max(row_id)
from    @tmp
 
while @i<=@max 
      begin
            select  @sql=replace(replace(@template, 'Replacetablename', quotename(schemaname)+'.'+quotename(tablename)), 'Replacecolumnname', columnname)
            from    @tmp
            where   row_id=@i
 
 
            print @sql
            exec SP_EXECUTESQL @sql, @params, @data_exists=@data_exists output
 
            if @data_exists=1 
               update @tmp
               set    datafound=1
               where  row_id=@i
 
            set @i=@i+1
      end
 
select  schemaname, tablename, columnname
from    @tmp
where   datafound=1
GO