Предлагаю вашему вниманию статью о ссылках и поиске путеводных нитей.

Когда-то темой одной из моих первых публикаций стал поиск конкретных столбцов, позволяющих определить, внутри какой таблицы (или таблиц) может находиться столбец в базе данных. Для решения задачи использовалось системное представление каталога 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