В одной из недавних статей я рассказал о том, как найти любую строку текста T-SQL внутри хранимых процедур; для этой цели использовалось представление sys.syscomments. Один из читателей заметил, что sys.syscomments как представление System Compatibility (существовавшее еще до появления SQL Server 2005) уже довольно давно объявлено устаревшим. Это не совсем так. Представления System Compatibility не объявлены устаревшими, но они сохраняются только с целью удовлетворить запросы разработчиков для SQL Server 2000, если основная база данных содержит таблицу с тем же именем, что и представления совместимости. Отсюда слово compatibility в названии. Эти представления никогда не меняются в схеме, не обновляются и вряд ли удаляются.
Появление представлений совместимости совпало по времени с выпуском SQL Server 2005. С тех пор существуют и системные представления SQL Server. Эти представления определены таким образом, чтобы обеспечить согласование структуры и оптимизировать удобные для использования соглашения об именовании SQL Server 2005, в частности соглашение об именовании, используемое в динамических объектах управления, появившихся в этом выпуске и поддерживаемых с тех пор. Схемы представлений системных каталогов по-прежнему будут развиваться вместе с продуктом. И на одно из этих представлений каталогов один из читателей обратил внимание как на лучшее решение, нежели sys.syscomments. Я склонен согласиться с этим мнением.
Почему sql_modules
Одним из недостатков, связанных с применением sys.syscomments, был тип данных в столбце, содержащем текст SQL, используемый в объекте. Это самый интересный столбец при поиске текста, используемого в определении объекта SQL. Определенным типом данных был varbinary с предельным размером 8000 байт. Это означает, что вам необходимо возвратить идентификатор col_id, чтобы объединить фрагменты текста SQL для возможных больших определений.
Кроме того, если условие поиска переходит через разграничительную линию в 8000 байт, то никаких результатов не возвращается.
Однако sql_modules сохраняет определение в столбце определений с типом данных nvarchar (max), снимая проблему ограничения в 8000 байт. У нас больше нет необходимости объединять результаты или беспокоиться о пропаже важного результата из-за поисковой строки, охватывающей две записи по разные стороны границы в 8000 байт.
Пока вносятся изменения: используйте sp_foreachdb
Процесс применения описываемых механизмов поиска предусматривает поиск во всех базах данных. Для этого я воспользовался недокументированной системной хранимой процедурой master.sys.sp_msforeachdb. Использование хранимой процедуры связано с неизбежными проблемами, в частности, с возможным пропуском одной или двух баз данных, что неприемлемо, если нужно выполнить поиск во всех базах данных.
Несколько лет назад обладатель звания SQL Server MVP Аарон Бертранд подготовил более удачную хранимую процедуру для поиска во всех базах данных, именуемую sys.sp_foreachdb (https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/). Я упоминал об этом инструменте в своих статьях, и это действительно более удачное решение, нежели использование недокументированной хранимой процедуры Microsoft. Приведенный в листинге 1 код, в сущности, вносит изменения в первоначальное решение с использованием sys.sql_modules и sys.sp_foreachdb. Я задействовал объект MS-provide, чтобы вам не приходилось устанавливать или создавать другие зависимые объекты. Теперь, после того как мы рассмотрели процесс поиска определений T-SQL для объектов, наступает время для установки решения Аарона Бертранда для оптимизации выполнения запросов к нескольким базам данных.
Эффективное решение для поиска определений объектов
Приведенный ниже программный код выполняет задачу, поставленную в первой статье, но с более надежным механизмом поиска в различных базах данных и без ограничения в 8000 байт в sys.comments.
Как отмечалось в предыдущей статье, я создал эту хранимую процедуру в базе данных обслуживания, чтобы поддерживать базу данных master в чистом состоянии. При вызове этой хранимой процедуры необходимо полностью определить ее с именем базы данных. Если вы предпочитаете изменить приведенный выше запрос для размещения в master и вызова из любого контекста базы данных, то сделать это можно, изменив имя базы данных на master и предварив имя хранимой процедуры символами sp_.
Поскольку внесены изменения в использование как sys.sql_modules, так и sp_foreachdb, я позаботился о том, чтобы добавить два необязательных параметра на случай, если потребуется фильтровать результаты по конкретному типу объектов (возможные значения показаны в приведенной таблице) и включить в круг объектов поиска системные объекты (is_ms_shipped = 1). Вы также можете не указывать значение параметра (NULL), и получите результаты для любого типа объектов с определением, содержащим поисковую строку.
Возможные значения типа объекта
В таблице приводятся возможные значения типа объекта для параметра @object_type.
.jpg) |
Примеры
Пример 1. Нет фильтрации по типу объекта.
В первом примере используется тот же критерий, что и в опубликованной в этом номере статье «Как найти строку в любой хранимой процедуре»: поиск ссылок на backupset. Не выполняется фильтрация по какому-либо типу объекта, и я хочу видеть в результатах исключительно пользовательские объекты. Каждый их трех примеров в листинге 2 приносит идентичные результаты (экран 1).
.jpg) |
| Экран 1. Результаты фильтрации по типу объекта |
Пример 2. Фильтрация созданных пользователем хранимых процедур.
В этом примере используется тот же критерий, что был упомянут выше, но на этот раз я хочу видеть только системные триггеры; результаты представлены на экране 2:
EXEC iDBA.dbo.search_sproc_text 'backupset', 'TR', 1;
.jpg){kind=small} |
| Экран 2. Фильтрация только созданных пользователем хранимых процедур |
Итак, благодаря внимательности читателя мне удалось усовершенствовать программный код и добавить функциональность для фильтрации объектов, предоставленных Microsoft или созданных пользователями, в частности по типу объектов. Таким образом, вы можете взять любой программный код из данной статьи или из Интернета и приспособить его для своих нужд, а также поделиться плодами своих экспериментов с другими членами сообщества на различных интернет-ресурсах.
USE iDBA;
GO
CREATE PROCEDURE search_sproc_text @searchforthis NVARCHAR(512), @object_type VARCHAR(2) = NULL, @is_ms_shipped BIT = 0 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,
object__name sysname NOT NULL,
object__type NVARCHAR(120) NOT NULL,
is_ms_shipped BIT NOT NULL,
sql__text NVARCHAR(MAX) NOT NULL
)
IF @object_type IS NULL
BEGIN
SELECT @search_text =
'USE ?;
INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text)
SELECT db_name() AS the__database
, OBJECT_SCHEMA_NAME(O.object_id) AS the__schema
, O.name AS object__name
, O.type_desc AS object__type
, O.is_ms_shipped
, M.definition AS sql__text
FROM sys.objects O WITH(NOLOCK)
LEFT JOIN sys.sql_modules M ON O.object_id = M.object_id
WHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND M.definition LIKE ''%' + @searchforthis + '%''' + ';'
END
ELSE
BEGIN
SELECT @search_text =
'USE ?;
INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text)
SELECT db_name() AS the__database
, OBJECT_SCHEMA_NAME(O.object_id) AS the__schema
, O.name AS object__name
, O.type_desc AS object__type
, O.is_ms_shipped
, M.definition AS sql__text
FROM sys.objects O WITH(NOLOCK)
LEFT JOIN sys.sql_modules M ON O.object_id = M.object_id
WHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND O.type = ''' + @object_type + '''' +
'AND M.definition LIKE ''%' + @searchforthis + '%''' + ';'
END
EXEC dbo.sp_foreachdb @command = @search_text;
SELECT the__database
, the__schema
, object__name
, object__type
, is_ms_shipped
, sql__text
FROM #search_results
ORDER BY the__database
, the__schema
, object__name
GO
EXEC iDBA.dbo.search_sproc_text 'backupset'; EXEC iDBA.dbo.search_sproc_text 'backupset', NULL, NULL; EXEC iDBA.dbo.search_sproc_text 'backupset', NULL, 0;