Поиск строки в базе MS SQL Server

Иногда, когда изучаешь софт, бывает необходимо найти некоторое значение в базе данных Microsoft SQL Server.

Вот вам скрипт, который выполняем в Query Editor над базой. В строчке "set @search_string = 'what.you.are.searching.for';" в кавычках пишем, что мы ищем.

/* 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 
;