Не так давно мы анонсировали бесплатную утилиту SQL Dynamite для поиска по метаинформации в базах данных. Хотим поблагодарить всех за хорошие отызвы, критику, найденные ошибки.
Для вас мы выпустили обновление, исправили найденные ошибки, и добавили поддержку SQLite и Informix.
Новая версия доступна здесь.
В прошлой статье мы обещали рассмотреть вопросы доступа к метаинформации БД. Сегодня поговорим про MS SQL и Sybase.
Процесс поиска и анализа связей различных объектов в СУБД Microsoft SQL Server, Sybase Adaptive Server Enterprise, Sybase Anywhere и SQL Azure прежде всего заключается в работе с метаданными.
Проблема в том что Sybase ASE и MSSQL, изначально имея одинаковую структуру метаданных, прошли долгий путь развития независимо друг от друга. Sybase Anywhere изначально была Watcom SQL и имела кучу отличий от Sybase ASE, а SQL Azure – это вообще облачная СУБД. Тем не менее, структура метаданных в них во многом схожа, в частности имеют одинаковые названия таблицы, содержащие ключевую информацию об объектах (sysobjects, syscomments и syscolumns).
В данной статье рассматривается структура метаданных СУБД следующих версий:
MSSQL 2005, Sybase ASE 15.7, Sybase Anywhere 12.0.1 и SQL Azure текущей на данный момент версии.
0) Общие для всех БД таблицы и хранимые процедуры
Таблицы:
1. sysobjects
Используется для хранения информации обо всех объектах в системе (ID, имя объекта, тип объекта и прочие параметры).
Основная таблица для работы с метаданными.
2. syscomments
Хранит в себе текст хранимых процедур, пользовательских функций, триггеров и просмотров (views).
3. syscolumns
Хранит в себе информацию о колонках каждой таблицы (имя, тип и пр.).
Хранимые процедуры:
4. sp_columns
Хранимая процедура выдающая информацию о колонках таблицы по её имени.
1) Таблицы и хранимые процедуры, специфические для нескольких СУБД:
Таблицы / views:
1. sysconstraints
(MSSQL & Sybase ASE) Хранит в себе информацию о первичных ключах, внешних ключах и подобных им объектах (unique keys, checks, defaults).
2. sys.sysindexes (view) или sysindexes (table)
(MSSQL & Sybase ASE & Sybase Anywhere) Хранит в себе информацию об индексах.
3. sys.sysforeignkeys
(MSSQL & Sybase Anywhere) Хранит в себе информацию о внешних ключах.
Хранимые процедуры:
4. sp_helpindex
(MSSQL & SQL Azure & Sybase ASE) Хранимая процедура выдающая информацию об индексах таблицы по её имени.
5. sp_helpconstraint
(MSSQL & Sybase ASE) Хранимая процедура выдающая информацию о внешних и первичных ключах таблицы по её имени.
Таблицы и хранимые процедуры, специфические для СУБД от Microsoft:
2) MSSQL
1. sp_MShelpcolumns
Это специфическая для MSSQL (как видно из названия) хранимая процедура которая выдаёт подробную информацию о колонках таблицы по её имени.
2. sp_MStablekeys
Это специфическая для MSSQL (как видно из названия) хранимая процедура которая выдаёт подробную информацию о первичных и внешних ключах таблицы по её имени.
3. sp_MStablechecks
Это специфическая для MSSQL (как видно из названия) хранимая процедура которая выдаёт подробную информацию об ограничениях таблицы по её имени.
3) MSSQL + SQL Azure
SQL Azure, так же как и Microsoft SQL Server (начиная с версии 2005) поддерживает INFORMATION_SCHEMA (кроме этих двух СУБД этот стандарт также поддерживается MySQL и PostgreSQL).
1. sys.indexes
Хранит в себе информацию об индексах и первичных ключах.
2. sys.sql_modules (только для SQL Azure)
Хранит в себе информацию о триггерах.
3. sys.triggers
Как и предыдущая таблица, хранит в себе информацию о триггерах.
4. sys.foreign_key_columns
Хранит в себе информацию о внешних ключах.
Кроме того MSSQL (начиная с версии 2005) поддерживает такую штуку как INFORMATION SCHEMA. Если вкратце – это стандарт хранения метаданных, заключающийся в том что СУБД должна иметь специальный набор read-only views которые выдают информацию по разных типам объектов. Примеры:
1. information_schema.views
(MSSQL & SQL Azure) Хранит в себе информацию о views.
2. information_schema.routines
(MSSQL & SQL Azure) Хранит в себе информацию о хранимых процедурах и функциях.
Как мы видим, несмотря на существенные различия в структуре метаданных, для анализа содержимого этих СУБД вполне могут быть применены (с определёнными ограничениями естественно) схожие подходы. Основная логика работы с ними (поиск вхождений, генерация DDL по метаданным) уже реализована в программе SQL Dynamite.
