Searching the string in MS SQL Server database

Sometimes, in troubleshooting and/or learning new software you need to find specific string or value in SQL database.

Here is the script to run in Query Editor over the base to find some string. In "set @search_string = 'what.you.are.searching.for';" in quotes you must type in what you search.

The script is compatible at least with SQL Server 2005/2008 ... hopefully with later versions also.

/* original script by Narayana Vyas Kondreddi, 2002 */ 
/* adapted by Oliver Holloway, 2009 */ 
/* these lines can be replaced by use of input parameter for a proc */ 
declare @search_string varchar(1000); 
set @search_string = 'what.you.are.searching.for'; 
/* create results table */ 
create table ##string_locations ( 
  table_name varchar(1000), 
  field_name varchar(1000), 
  field_value varchar(8000) 
) 
; 
/* special settings */ 
set nocount on 
; 
/* declare variables */ 
declare 
  @table_name varchar(1000), 
  @field_name varchar(1000) 
; 
/* variable settings */ 
set @table_name = '' 
; 
set @search_string = QUOTENAME('%' + @search_string + '%','''') 
; 
/* for each table */ 
while @table_name is not null 
begin 
  set @field_name = '' 
  set @table_name = ( 
    select MIN(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)) 
    from INFORMATION_SCHEMA.TABLES 
    where  
      table_type = 'BASE TABLE' and 
      QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) > @table_name and 
      OBJECTPROPERTY(OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), 'IsMSShipped') = 0 
  ) 
  /* for each string-ish field */ 
  while (@table_name is not null) and (@field_name is not null) 
  begin 
    set @field_name = ( 
      select MIN(QUOTENAME(column_name)) 
      from INFORMATION_SCHEMA.COLUMNS 
      where  
        table_schema    = PARSENAME(@table_name, 2) and 
        table_name  = PARSENAME(@table_name, 1) and 
        data_type in ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext') and 
        QUOTENAME(column_name) > @field_name 
    ) 
    /* search that field for the string supplied */ 
    if @field_name is not null 
    begin 
      insert into ##string_locations 
      exec( 
        'select ''' + @table_name + ''',''' + @field_name + ''',' + @field_name +  
        'from ' + @table_name + ' (nolock) ' + 
        'where patindex(' + @search_string + ',' + @field_name + ') > 0'  /* patindex works with char & text */ 
      ) 
    end 
    ; 
  end 
  ; 
end 
; 
/* return results */ 
select table_name, field_name, field_value from ##string_locations (nolock) 
; 
/* drop temp table */ 
drop table ##string_locations 
;