В одной из недавних статей я рассказал о том, как найти любую строку текста 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 для объектов, наступает время для установки решения Аарона Бертранда для оптимизации выполнения запросов к нескольким базам данных.

Эффективное решение для поиска определений объектов

Приведенный ниже программный код выполняет задачу, поставленную в первой статье, но с более надежным механизмом поиска в различных базах данных и...

Это не вся статья. Полная версия доступна только подписчикам журнала. Пожалуйста, авторизуйтесь либо оформите подписку.

Поделитесь материалом с коллегами и друзьями

Купить номер с этой статьей в PDF