Время на прочтение
5 мин
Количество просмотров 61K
Описание общей потребности в поиске данных и объектов в базе данных
Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.
Достаточно часто может возникнуть ситуация, при которой нужно найти:
- объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
- данные (значение и в какой таблице располагается)
- фрагмент кода в определениях объектов базы данных
Существует множество готовых решений как платных, так и бесплатных.
Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.
Поиск с помощью встроенных средств самой СУБД
Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:
Поиск таблицы по имени
select [object_id], [schema_id],
schema_name([schema_id]) as [schema_name],
[name],
[type],
[type_desc],
[create_date],
[modify_date]
from sys.all_objects
where [name]='Employee';
Результат может быть примерно такой:
Здесь выводятся:
- идентификаторы объекта и схемы, где располагается объект
- название этой схемы и название этого объекта
- тип объекта и описание этого типа объекта
- даты и время создания и последней модификации объекта
Чтобы найти все вхождения строки “Project”, то можно использовать следующий скрипт:
Поиск всех объектов по подстроке в имени
select [object_id], [schema_id],
schema_name([schema_id]) as [schema_name],
[name],
[type],
[type_desc],
[create_date],
[modify_date]
from sys.all_objects
where [name] like '%Project%';
Результат может быть примерно такой:
Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.
Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:
Поиск всех объектов по подстроке в имени с выводом родительских объектов
select ao.[object_id], ao.[schema_id],
schema_name(ao.[schema_id]) as [schema_name],
ao.parent_object_id,
p.[schema_id] as [parent_schema_id],
schema_name(p.[schema_id]) as [parent_schema_name],
p.[name] as [parent_name],
ao.[name],
ao.[type],
ao.[type_desc],
ao.[create_date],
ao.[modify_date]
from sys.all_objects as ao
left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
where ao.[name] like '%Project%';
Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:
В запросах используются следующие системные объекты:
- таблица sys.all_objects
- скалярная функция schema_name
Итак, разобрали как найти объекты в базе данных с помощью встроенных средств самой СУБД.
Теперь покажем как найти данные в базе данных на примере поиска строк.
Чтобы найти строковое значение по всем таблицам базы данных, можно воспользоваться следующим решением. Упростим данное решение и покажем как можно найти например значение “Ramiro” с помощью следующего скрипта:
Поиск строковых значений по подстроке во всех таблицах базы данных
set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'
declare @sql nvarchar(max);
create table #rslt
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))
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', 'int', 'tinyint') 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
set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column +
' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';
print @sql;
exec(@sql);
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
drop table #rslt
close s
deallocate s
Результат выполнения может быть таким:
Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.
Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:
- sys.sql_modules
- sys.all_sql_modules
- sys.syscomments
Например, используя последнее представление, можно с помощью следующего скрипта найти все объекты, в определениях которых встречается заданный фрагмент кода:
Поиск фрагмента кода в определениях объектов базы данных
select obj.[object_id],
obj.[name],
obj.[type_desc],
sc.[text]
from sys.syscomments as sc
inner join sys.objects obj on sc.[id]=obj.[object_id]
where sc.[text] like '%code snippet%';
Здесь будет выведен идентификатор, название, описание и полное определение объекта.
Поиск с помощью бесплатной утилиты dbForge Search
Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.
Для вызова этой утилиты в окне SSMS нажмите на кнопку 
.
Появится следующее окно поиска:
Обратите внимание на верхнюю панель (слева направо):
- можно переключать режим поиска (ищем DDL (объекты) или данные)
- непосредственно что ищем (какую подстроку)
- учитывать ли регистр, искать точное соответствие слову, искать вхождения:
- группировать результат по типам объектов — кнопка
- выбрать нужные типы объектов для поиска:
- также можно задать несколько баз данных для поиска и выбрать экземпляр MS SQL Server
Это все в режиме поиска объектов, т е когда включен DDL:
В режиме поиска данных изменится только выбор типов объектов:
А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:
Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:
Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.
При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.
Также можно переместить навигацию на найденный объект, щелкнув на кнопку 
:
Можно также сгруппировать найденные объекты по их типу:
Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.
Теперь найдем значение “Ramiro” по всем таблицам:
Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.
Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :
Таким образом мы можем искать нужные объекты и данные в базе данных.
Заключение
Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.
Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.
Источники
- Search_Script.sql
- SSMS
- dbForge Search
- Документация по Microsoft SQL
- Бесплатные решения от компании Devart
Очень часто разработчики и администраторы БД сталкиваются с задачей поиска в базе данных всех упоминаний какого-либо объекта, столбца, переменной или поиск всех таблиц, где встречается искомое значение. Если вам приходилось решать подобную проблему, то вы знаете, что это ни самая тривиальная задача и Ctrl+F здесь не поможет.
Готового решения нет ни в SQL Server Management Studio ни в Visual Studio, вот несколько сценариев, которые вы можете использовать:
Поиск данных в таблицах и представлениях
Есть много реализаций на T-SQL поиска данных по всем таблицам с просмотром всех столбцов и это не самая оптимальная реализация, так как везде используется перебор в курсоре системных представлений.
DECLARE @SearchText varchar(200), @Table varchar(100), @TableID int, @ColumnName varchar(100), @String varchar(1000); SET @SearchText = 'John'; DECLARE CursorSearch CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'; OPEN CursorSearch; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CursorColumns CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @TableID AND system_type_id IN(167, 175, 231, 239); OPEN CursorColumns; FETCH NEXT FROM CursorColumns INTO @ColumnName; WHILE @@FETCH_STATUS = 0 BEGIN SET @String = 'IF EXISTS (SELECT * FROM ' + @Table + ' WHERE ' + @ColumnName + ' LIKE ''%' + @SearchText + '%'') PRINT ''' + @Table + ', ' + @ColumnName + ''''; EXECUTE (@String); FETCH NEXT FROM CursorColumns INTO @ColumnName; END; CLOSE CursorColumns; DEALLOCATE CursorColumns; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; END; CLOSE CursorSearch; DEALLOCATE CursorSearch;
У этого решения есть много недостатков:
- Использование курсоров, а это, как правило неэффективный код
- Сложный запрос, который медленно работает даже на небольших базах данных
- Поиск работает только по текстовым данным, поэтому для поиска, например, даты потребуется доработка
Поиск объектов
Поиск объектов в БД по имени или их упоминание в других объектах немного проще, чем поиск определённого текста. Есть так же несколько разных сценариев поиска, но все их объединяет одно: обращение к системным объектам.
Во всех следующих сценариях осуществляется поиск переменной @StartProductID в хранимых процедурах. Но скрипты можно использовать и для поиска в других объектах – в триггерах, функциях, столбцах и т.д.
INFORMATION_SCHEMA.ROUTINES
Системное представление INFORMATION_SCHEMA.ROUTINES позволяет найти любой параметр, встречающийся в процедурах или функциях. Колонка ROUTINE_DEFINITION содержит полный текст объекта, который был указан при его создании.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%' AND ROUTINE_TYPE='PROCEDURE'
Результат работы запроса:
Не используйте представления INFORMATION_SCHEMA, чтобы определить схему объекта. Единственный надежный способ найти схему объекта — выполнить запрос к представлению каталога sys.objects.
Представление sys.syscomments
Содержит записи для всех представлений, правил, значений по умолчанию, триггеров, ограничений CHECK и DEFAULT, а также для всех хранимых процедур в базе данных. Столбец text содержит инструкции исходных определений SQL.
SELECT OBJECT_NAME( id ) FROM SYSCOMMENTS WHERE text LIKE '%@StartProductID%' AND OBJECTPROPERTY(id , 'IsProcedure') = 1 GROUP BY OBJECT_NAME( id );
Результат:
Этот метод не желательно использовать, так как в будущих версиях SQL Server представление sys.syscomments будет удалено.
Представление sys.sql_modules
Содержит по одной строке для каждого объекта, являющегося модулем, определенным на языке SQL в SQL Server.
SELECT OBJECT_NAME( object_id ) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id , 'IsProcedure') = 1 AND definition LIKE '%@StartProductID%';
Результат такой же, как в предыдущем способе:
Другие представления информационной схемы
Запрос к представлениям sys.syscomments, sys.schemas и sys.objects. Представление sys.schemas содержит информацию обо всех схемах внутри базы данных. В представление sys.objects содержится информация обо всех объектах базы данных. Обратите внимание, что для поиска информации о триггерах необходимо просматривать отдельное представление sys.triggers.
DECLARE @searchString nvarchar( 50 ); SET@searchString = '@StartProductID'; SELECT DISTINCT s.name AS Schema_Name , O.name AS Object_Name , C.text AS Object_Definition FROM syscomments C INNER JOIN sys.objects O ON C.id = O.object_id INNER JOIN sys.schemas S ON O.schema_id = S.schema_id WHERE C.text LIKE '%' + @searchString + '%' OR O.name LIKE '%' + @searchString + '%' ORDER BY Schema_name , Object_name;
Полученный результат:
Основным недостатком данных методов поиска является то, что для поиска каждого нового типа объектов необходимо вносить в скрипты изменения. Чтобы сделать это вы должны хорошо понимать внутреннюю организацию и структуру системных объектов SQL Server. Кроме того, нужно позаботиться об обработке различных ошибок и исключений, например, связанных с поиском строк, содержащих экранирующие символы.
Если вы не являетесь опытным разработчиком, не знакомы с внутренним устройством хранения DDL информации объектов БД или предпочитаете использовать проверенное и безошибочное решение, то начните использовать ApexSQL Search.
ApexSQL Search – это надстройка (ADD-IN) для SSMS и Visual Studio, которая позволяет искать любой текст в объектах базы данных (в том числе имена объектов), данные, хранящиеся в таблицах и представлениях (даже если они зашифрованы), осуществлять повторные поиски по истории в один клик.
Для поиска данных в таблицах и представлениях:
- В меню SQL Server Management Studio или Visual Studio найдите ApexSQL Search
-
Выберите вариант Database text search…:
- В текстовом поле поиска Search text укажите искомый текст.
- В раскрывающемся меню Database выберите базу данных для поиска
- В дереве поиска Select objects to search укажите таблицы и представления для поиска или оставьте их все выделенными
- С помощью флажков укажите в каких типах данных необходимо осуществить поиск (numeric, text type, uniqueidentifier, date columns), искать ли в представлениях, необходимо ли строгое совпадение и, при поиске даты, укажите её формат.
-
После нажатия кнопки Find now, вы получите сводную таблицу со списком таблиц и представлений, которые содержат искомое значение:
- Нажмите кнопку с многоточием в колонке Column value, чтобы получить детали:
Для поиска объектов:
- В меню SQL Server Management Studio или Visual Studio найдите ApexSQL Search
-
Выберите вариант Database object search…:
- В поле поиска Search text укажите искомый объект, например, имя переменной.
- В раскрывающемся меню Database выберите базу данных для поиска
- В дереве поиска Objects укажите типы объектов для поиска или оставьте их все выделенными
- Флажками укажите детали поиска: искать ли в именах объектов, колонок, индексов или только в самих описания объектов. Просматривать ли системные объекты, нужно ли точное совпадение, а также можно указать экранирующий символ.
- После этого начинаем поиск Find now:
В таблице будет полный список объектов, которые содержат искомое значение.
- При двойном щелчке по объекту в таблице Database object search, можно увидеть его ссылку в Object Explorer
SQL Server Management Studio и Visual Studio не имеют встроенной возможности поиска объектов и данных в БД. Запросы, которые решают эту задачу неэффективны, медленные в работе и требуют глубоких знаний системных объектов SQL Server. Но зато с этой задачей прекрасно справляется ApexSQL Search
Переводчик: Алексей Князев
November 20, 2015
С помощью данного скрипта под MS SQL можно выполнять поиск по содержимому/тексту хранимых процедур, функций, триггеров и т.п.:
SELECT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like '%search_string%'
В поле type_desc содержится название сущности, в которой была найдена искомая строка.
There are lots of workable answers already. Just thought I would add one I came up with that has a lot of optional funcionality.
--=======================================================================
-- MSSQL Unified Search
-- Minimum compatibility level = 130 (SQL Server 2016)
-- NOTE: The minimum compatibility level is required by the built-in STRING_SPLIT() function.
-- However, you can create the STRING_SPLIT() function at the bottom of this script for
-- lower versions of MSSQL Server.
--
-- Usage:
-- Set the parameters below and execute this script.
--
/************************ Enter Parameters Here ************************/
/**/
/**/ DECLARE @SearchString VARCHAR(1000) = 'string to search for'; -- Accepts SQL wilcards
/**/
/**/ DECLARE @IncludeUserTables BIT = 1;
/**/ DECLARE @IncludeViews BIT = 0;
/**/ DECLARE @IncludeStoredProcedures BIT = 0;
/**/ DECLARE @IncludeFunctions BIT = 0;
/**/ DECLARE @IncludeTriggers BIT = 0;
/**/
/**/ DECLARE @DebugMode BIT = 0;
/**/ DECLARE @ExcludeColumnTypes NVARCHAR(500) = 'text, ntext, char, nchar, timestamp, bigint, tinyint, smallint, bit, date, time, smalldatetime, datetime, datetime2, real, money, float, decimal, binary, varbinary, image'; -- Comma delimited list
/**/
/***********************************************************************/
SET NOCOUNT ON;
SET @SearchString = QUOTENAME(@SearchString,'''');
DECLARE @Results TABLE ([ObjectType] NVARCHAR(200), [ObjectName] NVARCHAR(200), [ColumnName] NVARCHAR(400), [Value] NVARCHAR(MAX), [SelectStatement] NVARCHAR(1000));
DECLARE @ExcludeColTypes TABLE (system_type_id INT);
INSERT INTO @ExcludeColTypes ([system_type_id])
SELECT [system_type_id]
FROM sys.types WHERE
[name] IN (
SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@ExcludeColumnTypes,',')
);
DECLARE @ObjectType NVARCHAR(200);
DECLARE @ObjectName NVARCHAR(200);
DECLARE @Value NVARCHAR(MAX);
DECLARE @SelectStatement NVARCHAR(1000);
DECLARE @Query NVARCHAR(4000);
/********************* Table Objects *********************/
IF (@IncludeUserTables = 1)
BEGIN
DECLARE @TableObjectId INT = (SELECT MIN([object_id]) FROM sys.tables);
DECLARE @ColumnId INT;
WHILE @TableObjectId IS NOT NULL
BEGIN
SELECT @ObjectType = 'USER TABLE';
SELECT @ObjectName = '[' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME(@TableObjectId) + ']' FROM sys.tables WHERE [object_id] = @TableObjectId;
SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId);
WHILE @ColumnId IS NOT NULL
BEGIN
SELECT @Value = '[' + [name] +']' FROM sys.columns WHERE [object_id] = @TableObjectId AND column_id = @ColumnId;
SET @SelectStatement = 'SELECT * FROM ' + @ObjectName + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';
SET @Query = 'SELECT '
+ QUOTENAME(@ObjectType, '''')
+ ', ' + QUOTENAME(@ObjectName, '''')
+ ', ' + QUOTENAME(@Value, '''')
+ ', ' + @Value
+ ', ''' + REPLACE(@SelectStatement,'''','''''') + ''''
+ ' FROM ' + @ObjectName
+ ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';
IF @DebugMode = 0
BEGIN
INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
PRINT 'Select Statement: ' + @SelectStatement;
PRINT 'Query: ' + @Query;
END;
SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId AND [column_id] > @ColumnId);
END;
SET @TableObjectId = (SELECT MIN([object_id]) FROM sys.tables WHERE [object_id] > @TableObjectId);
END;
END;
/********************* Objects Other than Tables *********************/
SET @Query = 'SELECT ' +
'ObjectType = CASE ' +
'WHEN b.[type] = ''V'' THEN ''VIEW'' ' +
'WHEN b.[type] = ''P'' THEN ''STORED PROCEDURE'' ' +
'WHEN b.[type] = ''FN'' THEN ''SCALAR-VALUED FUNCTION'' ' +
'WHEN b.[type] = ''IF'' THEN ''TABLE-VALUED FUNCTION'' ' +
'WHEN b.[type] = ''TR'' THEN ''TRIGGER'' ' +
'END ' +
',[ObjectName] = ''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'' ' +
',[ColumnName] = NULL ' +
',[Value] = a.[definition] ' +
',[SelectStatement] = ''SP_HELPTEXT '' + QUOTENAME(''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'','''''''') + '';'' ' +
'FROM [sys].[sql_modules] a ' +
'JOIN [sys].[objects] b ON a.[object_id] = b.[object_id] ' +
'WHERE ' +
'( ' +
' a.[definition] LIKE ' + @SearchString +
') ' +
'AND ' +
'( ' +
' ( ' +
CAST(@IncludeViews AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''V'') ' +
' ) ' +
' OR ' +
' ( ' +
CAST(@IncludeStoredProcedures AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''P'') ' +
' ) ' +
' OR ' +
' ( ' +
CAST(@IncludeFunctions AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''FN'',''IF'') ' +
' ) ' +
' OR ' +
' ( ' +
CAST(@IncludeTriggers AS VARCHAR(1)) + ' = 1 ' +
' AND ' +
' b.[type] IN (''TR'') ' +
' ) ' +
'); ';
IF @DebugMode = 0
BEGIN
INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
PRINT 'Select Statement: ' + @SelectStatement;
PRINT 'Query: ' + @Query;
END;
IF @DebugMode = 0
BEGIN
SELECT
[ObjectType]
,[ObjectName]
,[ColumnName]
,[Value]
,[Count] = CASE
WHEN [ObjectType] IN ('USER TABLE') THEN COUNT(1)
ELSE NULL
END
,[SelectStatement]
FROM @Results
GROUP BY [ObjectType], [ObjectName], [ColumnName], [Value], [SelectStatement]
ORDER BY [Value];
END;
/********************** STRING_SPLIT() FUNCTION **********************
CREATE FUNCTION STRING_SPLIT (
@Expression nvarchar(4000)
,@Delimiter nvarchar(100)
)
RETURNS @Ret TABLE ([value] NVARCHAR(4000))
AS
BEGIN
DECLARE @Start INT = 0, @End INT, @Length INT;
SELECT @End = CHARINDEX(@Delimiter,@Expression), @Length = @End - @Start;
IF @End <= 0
BEGIN
INSERT INTO @Ret ([value]) VALUES (@Expression);
END
ELSE
BEGIN
WHILE @Length >= 0
BEGIN
INSERT INTO @Ret ([value])
SELECT ltrim(rtrim(substring(@Expression,@Start,@Length)));
SELECT @Start = @End + LEN(@Delimiter)
SELECT @End = CHARINDEX(@Delimiter,@Expression,@Start)
IF @End < 1
SELECT @End = LEN(@Expression) + 1;
SELECT @Length = @End - @Start;
END;
END;
RETURN;
END;
*********************************************************************/
Создаем хранимую процедуру, которую можно использовать для обнаружения ссылок на объект и выяснения структуры кода
Предлагаю вашему вниманию статью о ссылках и поиске путеводных нитей.
Когда-то темой одной из моих первых публикаций стал поиск конкретных столбцов, позволяющих определить, внутри какой таблицы (или таблиц) может находиться столбец в базе данных. Для решения задачи использовалось системное представление каталога sys.all_columns (http://sqlmag.com/database-administration/using-allcolumns-system-view-sql-server). Поводом для написания статьи стало то, что мне, как администратору баз данных, приходилось обслуживать более 2000 отдельных баз данных, разработанных как внутри компании, так и сотнями независимых поставщиков программного обеспечения. Каждая компания-разработчик — и разработчик внутри компании — имеет собственный стиль программирования. Поиск ошибок внутри незнакомого кода — одна из задач специалиста по данным, которой приходится уделять время регулярно. Целью той давней статьи была идентификация мест, где существуют столбцы для устранения проблемы, влияющей на время непрерывной работы продукта, предназначенного для области здравоохранения.
Прошли годы, и я оказался в новой роли и в другой компании. Я по-прежнему использую программный код из той статьи, но мне еще необходимо понять, каким образом группы разработки используют хранимые процедуры в продукте, поскольку на меня возложены обязанности не только администратора, но и программиста, и проектировщика баз данных.
Чтобы выполнять диагностику проблем и разрабатывать новый программный код, мне было важно создать сценарий поиска для идентификации конкретных объектов, структур кода или предикатов.
Хранимая процедура для поиска хранимых процедур
Итак, моя цель в данном случае — создать хранимую процедуру, которую можно использовать для обнаружения любых ссылок на объект, выяснения структуры кода и т. д. У меня есть строка, которую нужно найти во всех базах данных, чтобы возвратить результаты для любой хранимой процедуры с использованием строки, переданной в качестве переменной для поиска.
Просто знать имя хранимой процедуры недостаточно. Поэтому конечный результат будет содержать следующие столбцы:
- имя базы данных;
- имя схемы;
- имя хранимой процедуры;
- код хранимой процедуры.
Чтобы построить такую хранимую процедуру, потребуется несколько системных объектов: два системных представления каталога и одна недокументированная системная хранимая процедура.
Начнем с системных представлений каталога.
- sys.procedures. Это системное представление собирает метаданные обо всех хранимых процедурах в базе данных. Каждая база данных имеет представление каталога sys.procedures. Возвращаются только результаты для содержимого базы данных, в котором оно размещено.
- sys.syscomments. Это системное представление хранит исходный текст для каждой хранимой процедуры. В случае sys.procedures, sys.comments также действует в масштабе базы данных, поэтому предоставляет лишь сведения об объектах собственной базы данных.
Недокументированная системная хранимая процедура — sys.sp_MSforeachdb используется потому, что два системных представления каталога действуют в масштабе базы данных, следовательно, необходим способ возвращать результаты для этого запроса из всех баз данных. Вместе с Microsoft SQL Server поставляется недокументированная хранимая процедура в базе данных master, которая обеспечивает соответствующую функциональность. Вы можете передать параметр для команды T-SQL, которую нужно запустить для каждой базы данных, и результаты возвращаются как отдельные наборы записей для каждой базы данных, к которой применяется программный код.
Объединяем элементы
Теперь, когда мы знаем, из чего состоит решение, пришло время объединить все элементы. Программный код показан в листинге, а здесь мы рассмотрим детали и действующий пример.
В первую очередь обратите внимание, что я создаю эту хранимую процедуру в базе данных с именем iDBA. Каждому, кто знаком с моими статьями за несколько лет, известно, что я использую эту базу данных для всех административных сценариев. В вашей среде у нее может быть другое имя, но важно иметь базу данных, которая поможет не пропустить несистемные объекты в базу данных master, где ленивые программисты и администраторы баз данных, выбирающие легкие пути, хранят свой программный код.
Для этой хранимой процедуры требуется единственный параметр, чтобы передать строку для поиска — @searchforthis. Внутри сценария параметр заключен между символами %, и вам не нужно возиться со строкой поиска, зная, что вы можете получить необходимые результаты с меньшим по размеру критерием.
Хранимая процедура выполняет поиск во всех базах данных, поэтому я создал временную таблицу для хранения результатов, которые впоследствии передаются конечному пользователю. Для этого используется #search_results. Назначение первых четырех столбцов очевидно, но последний столбец (colid) может показаться странным. Этот столбец (полученный из sys.syscomments) необходим, потому что хранимые процедуры могут быть как простыми, так и довольно сложными. Программный код, составляющий хранимую процедуру, иногда приходится хранить в нескольких строках в sys.syscomments. Внутри sys.syscomments можно идентифицировать объект, которому принадлежит текст хранимой процедуры, взглянув на столбец id. Он сопоставляется object_id в системных таблицах. Значение colid — столбец, который начинается с 1, относящийся к порядку комментариев, составляющих полный текст хранимой процедуры. Если направить запрос напрямую sys.syscomments, то можно увидеть несколько строк для id = 123456, которые ссылаются на некоторый object_id для хранимой процедуры. Каждая строка для id = 123456 в sys.syscomments будет иметь нарастающее значение colid, которое упорядочивает полный текст хранимой процедуры, поэтому за id = 123456, colid = 1 следует id = 123456, colid = 2 и id = 123456, colid = 3, пока не будет охвачена вся команда, составляющая хранимую процедуру.
Итак, в сущности, хранимая процедура строит динамический запрос, объединяющий sys.procedures и sys.syscomments по object_id и id, как показано выше, где комментарии содержат критерий поиска. Этот текст запроса затем передается в недокументированную хранимую процедуру sp_MSforeachdb как параметр и проверяет базы данных по одной. Все «совпадения» для баз данных на сервере передаются во временную таблицу и возвращаются после опроса всех баз данных.
Процедура в действии
После создания хранимой процедуры выполняется следующий запрос. Я указываю имя базы данных, поэтому не имеет значения, какая база данных активна в момент запуска сценария:
EXEC iDBA.dbo.search_sprocs 'backupset';
В данном случае используется backupset, так как я подготовил хранимые процедуры в нескольких базах данных, которые ссылаются на это представление в базе данных msdb для идентификации резервных метаданных. В этом случае я рассчитываю также получить совпадения в системных базах данных, поскольку в качестве поискового текста используется имя системного представления. Результаты выглядят примерно так, как показано на приведенном экране.
 |
| Экран. Результаты поиска хранимых процедур |
Таким образом, имейте в виду, что существуют сторонние инструменты, функциональность которых совпадает с описанной в статье. Не каждая компания располагает средствами для их приобретения. Этот программный код с простой структурой отличается богатыми возможностями расширения и поможет вам сэкономить немало времени при поиске ссылок в любом количестве баз данных SQL Server.
Листинг. Хранимая процедура для поиска хранимых процедур
USE iDBA;
GO
CREATE PROCEDURE search_sprocs @searchforthis NVARCHAR(512) As
DECLARE @search_text NVARCHAR(MAX)
IF OBJECT_ID('tempdb..#search_results') IS NOT NULL
BEGIN
DROP TABLE #search_results;
END
CREATE TABLE #search_results
(
the__database sysname NOT NULL,
the__schema sysname NOT NULL,
procedure__name sysname NOT NULL,
procedure__text NVARCHAR(4000) NOT NULL,
colid int NOT NULL
)
SELECT @search_text =
'USE ?;
INSERT INTO #search_results (the__database, the__schema, procedure__name, procedure__text, colid)
SELECT db_name() AS the__database
, OBJECT_SCHEMA_NAME(P.object_id) AS the__schema
, P.name AS procedure__name
, C.text AS procedure__text
, C.colid
FROM sys.procedures P WITH(NOLOCK)
LEFT JOIN sys.syscomments C ON P.object_id = C.id
WHERE C.text LIKE ' + '''' + '%' + @searchforthis + '%' + '''' + ';'
EXEC sys.sp_MSforeachdb @command1 = @search_text;
SELECT the__database
, the__schema
, procedure__name
, procedure__text
FROM #search_results
ORDER BY the__database
, the__schema
, procedure__name
, colid;
GO