SQL-скрипты для поиска данных

Иногда мы точно знаем что и где искать, иногда только приблизительно знаем что и лишь догадываемся где. Хочу поделиться несколькими скриптами под MS SQL Server для последнего случая.

Поиск объектов во всех базах данных сервера

У вас бывало, что нужно найти таблицу чтобы посмотреть данные или структуру, а вы не помните точно в какой БД она находится? У меня, периодически, такое случается. Пример - найти таблицы из проекта пятилетней давности, чтобы посмотреть как тогда решал текущую задачу.

К счастью, есть процедура sp_MSforeachdb, которая позволяет написать такой скрипт довольно компактно:

DECLARE @name AS SYSNAME,
        @strSQL AS VARCHAR(MAX)
SET @name = 'Hierarchy'

SET @strSQL = '
IF EXISTS(SELECT * FROM ?..sysobjects WHERE name LIKE ''%'+@name+'%'')
BEGIN
    SELECT ''?'' db, name FROM ?..sysobjects WHERE name LIKE ''%'+@name+'%''
END
'
CREATE TABLE #result(
    db SYSNAME,
    name SYSNAME,
    PRIMARY KEY(db,name))

INSERT #result EXEC sp_MSforeachdb @strSQL
SELECT * FROM #result

DROP TABLE #result

Здесь всё достаточно просто:

1. Задаём часть названия, по которому будем искать (в данном случае - Hierarchy).
2. Собираем динамический SQL, который ищет по sysobjects (для некоторых старых версий MS SQL можно вместо VARCHAR(MAX) использовать VARCHAR(2000), например).
   При этом символ "?" заменится на название БД.
3. С помощью конструкции INSERT ... EXEC сохраняем все результаты во временную таблицу, чтобы отобразить, в итоге, единый результат.

Поиск подстроки в текстах хранимых процедур

Как вы знаете, тексты хранимых процедур, триггеров и представлений можно найти в таблице syscomments.

Чтобы наш предыдущий запрос позволил нам найти упоминание в syscomments некоторой подстроки, его можно переписать следующим образом:

SET @strSQL = '
SELECT DISTINCT ''?'' as db, o.name  FROM [?].dbo.syscomments s
JOIN [?].dbo.sysobjects o ON o.id = s.id WHERE s.[text] LIKE ''%'+@name+'%'''

Обратите внимание на DISTINCT - может пригодиться, потому что иногда для одного объекта бывает несколько записей в syscomments.

Поиск подстроки во всех строковых полях всех таблиц

Такой скрипт здорово помогает спать спокойно после "чистки" базы перед отправкой клиенту. Естественно, этой областью его применение не ограничивается.

Для этого случая помощник в виде процедуры от MS не нашёлся, поэтому пришлось использовать курсоры:

declare @pattern as nvarchar(MAX)
set @pattern = N'%Test%'
set nocount on
declare @sql as nvarchar(MAX)
declare @table as sysname
declare tables cursor local static read_only for select name from sys.tables t where t.is_ms_shipped = 0
open tables
fetch next from tables into @table
create table #results(name sysname not null, value nvarchar(MAX) not null)
while @@FETCH_STATUS = 0
begin
    set @sql = ''
    select @sql = @sql + '
    insert into #results select ''' + @table + '.' + name + ''' as name, [' + name + '] from [' + @table + '] where [' + name + '] like ''' + @pattern + ''''
        from sys.columns c where c.object_id = OBJECT_ID(@table) and c.system_type_id in 
        (select system_type_id from sys.types where collation_name is not null)
    exec(@sql)
    fetch next from tables into @table
end
select * from #results
close tables
drop table #results

Помимо курсоров принципы те же. Есть пара нюансов:

• Ищем таблицы без флага is_ms_shipped, чтобы просматривать только таблицы, созданные на этапе разработки.
• Для отбора строковых столбцов вместо списка типов (который поддерживать утомительно) используется фильтр "collation_name is not null".
  

Резюме

Внимательный читатель наверняка догадался по оформлению, что последний пример написан не в то же время что и два первых. Если быть точнее, последний запрос я написал месяц назад. Это я к тому, что на основе подобного подхода вы можете сами писать полезные скрипты, решающие ваши специфические задачи.

Если такой формат понравится читателям, буду иногда выкладывать полезные скрипты из личной коллекции.

Если у вас есть замечания, пожелания или новые темы – пишите в комментариях, твиттер или на olegaxenow.reformal.ru. Постараюсь учесть.