Структура метаданных в СУБД от Microsoft и Sybase

в 8:42, , рубрики: ddl search, dml search, grep, mysql, sql, tsql, Блог компании ХостТрекер, поиск метаинформации, метки: , , , , , ,

Структура метаданных в СУБД от Microsoft и Sybase
Не так давно мы анонсировали бесплатную утилиту SQL Dynamite для поиска по метаинформации в базах данных. Хотим поблагодарить всех за хорошие отызвы, критику, найденные ошибки.
Для вас мы выпустили обновление, исправли найденные ошибки, и добавили поддрежку с SQLite и Informix.

Новая версия доступна здесь.

В прошлой статье мы обещали рассмотреть вопросы доступа к метаинформации БД. Сегодня поговорим про MS SQL и Sysbase.

Процесс поиска и анализа связей различных объектов в СУБД 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.

Автор: fire_lizard

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js