SQL. Поиск любого текста или целого числа в БД

Небольшие запросы для поиска любого числа или текста в нужной базе данных MS SQL.

Поиск целого числа.


set nocount on
declare @name varchar(128), @substr bigint, @column varchar(128)
set @substr = 462083 -- ИСКОМОЕ ЦЕЛОЕ ЧИСЛОВОЕ ЗНАЧЕНИЕ
/* Создаем таблицу для вывода */
create table #rslt2
(table_name varchar(128), field_name varchar(128), value bigint)
/* Заполняем созданную таблицу найдеными данными */
declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
declare c cursor for
      select quotename(column_name) as column_name from information_schema.columns
           where data_type in ('int', 'bigint') and table_name = @name
set @name = quotename(@name)
open c
fetch next from c into @column
while @@fetch_status = 0
begin
      print 'Processing table - ' + @name + ', column - ' + @column
      exec('insert into #rslt2 select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column +
           ' from' + @name + ' where ' + @column + ' = ' + @substr )
fetch next from c into @column
end
close c
deallocate c
fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt2
group by table_name, field_name
order by table_name, field_name
/* Отображаем найденые значения если нужно(раскоментить следующую строку) */
-- select * from #rslt2 order by table_name, field_name
drop table #rslt2
close s
deallocate s

Поиск строки.


set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Пружинкин%' -- ФРАГМЕНТ ИСКОМОЙ СТРОКИ
/* Создаем таблицу для вывода */
create table #rslt
(table_name varchar(128), field_name varchar(128), value ntext)
/* Заполняем созданную таблицу найдеными данными */
declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
declare c cursor for
      select quotename(column_name) as column_name from information_schema.columns
           where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname') and table_name = @name
set @name = quotename(@name)
open c
fetch next from c into @column
while @@fetch_status = 0
begin
      print 'Processing table - ' + @name + ', column - ' + @column
      exec('insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column +
           ' from' + @name + ' where ' + @column + ' like ''' + @substr + '''')
      fetch next from c into @column
end
close c
deallocate c
fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name
/* Отображаем найденые значения если нужно (раскоментить следующую строку) */
--select * from #rslt order by table_name, field_name
drop table #rslt
close s
deallocate s