По материалам статьи Bill Graziano: Using SQL Server's Metadata

Представления информационных схем являются частью стандарта SQL-92. Этот стандарт определяет ряд представлений, которые призваны предоставлять информацию о базе данных. Например, есть представление по имени TABLES, которое предоставляет информацию о таблицах базы данных. Вы можете сделать запрос к этому представлению, точно так же как и к любому другому представлению. Например:

SELECT * FROM pubs.INFORMATION_SCHEMA.TABLES

Этот запрос возвратит информацию обо всех таблицах и представлениях в базе данных pubs:

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE 
------------- ------------ ---------- ---------- 
pubs          dbo          authors    BASE TABLE
pubs          dbo          discounts  BASE TABLE
...
pubs          dbo          titleview  VIEW

Кроме обычного набора таблиц могут быть отображены несколько системных, которые используются для репликации. Это такие таблицы, как sysarticles и syspublications. TABLE_CATALOG - это имя базы данных, а TABLE_SCHEMA - владелец объекта. Обратите внимание, что владельцем представления INFORMATION_SCHEMA является dbo.
Другим интересным представлением является COLUMNS. Например:

SELECT TABLE_CATALOG,
       TABLE_SCHEMA,	
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH
FROM pubs.INFORMATION_SCHEMA.columns
WHERE table_name = 'authors'

Возвратит информацию обо всех столбцах таблицы authors:

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE  CHARACTER_MAXIMUM_LENGTH 
------------- ------------ ---------- ----------- ---------- ------------------------ 
pubs          dbo          authors    au_id       varchar    11
pubs          dbo          authors    au_lname    varchar    40
pubs          dbo          authors    au_fname    varchar    20
pubs          dbo          authors    phone       char       12
pubs          dbo          authors    address     varchar    40
pubs          dbo          authors    city        varchar    20
pubs          dbo          authors    state       char       2
pubs          dbo          authors    zip         char       5
pubs          dbo          authors    contract    bit        NULL

На практике, можно получить немногим больше информации, чем из этого представления, результаты работы которого представлены выше. Представление выводит по одной строке для каждого столбца таблицы в порядке его позиции; показывает допустимость null-значений; видна точность числовых значений; можно увидеть значения по умолчанию; какой используется набор символов; тип сортировки, его порядок и любую другую информацию относительно определяемого пользователем типа данных. Ниже представлен список всех представлений INFORMATION_SCHEMA:

CHECK_CONSTRAINTS - Предоставляет информацию обо всех ограничениях в базе данных. COLUMN_DOMAIN_USAGE - Показывает, какая колонка и в какой таблице является определяемым пользователем типом данных. COLUMN_PRIVILEGES - Выводит по одной строке для каждой привилегии уровня столбца, предоставленной текущему пользователю. COLUMNS - Выводит по одной строке для каждого столбца в каждой таблице или представлении базы данных. CONSTRAINT_COLUMN_USAGE - Выводит по одной строке для каждого столбца, которому сопоставлено ограничение. CONSTRAINT_TABLE_USAGE - Выводит по одной строке для каждой таблицы, которая имеет ограничения. DOMAIN_CONSTRAINTS - Выводит определяемые пользователем типы данных, которым сопоставлены правила. DOMAINS - Выводит определяемый пользователем тип данных. KEY_COLUMN_USAGE - Выводит по одной строке для каждого столбца, который определен как ключ. PARAMETERS - Выводит по одной строке для каждого параметра в хранимой процедуре или определяемой пользователем функции. REFERENTIAL_CONSTRAINTS - Выводит по одной строке для каждого ограничения внешнего ключа. ROUTINES - Выводит по одной строке для каждой хранимой процедуры или определяемой пользователем функции. ROUTINE_COLUMNS - Выводит по одной строке для каждого столбца, возвращаемого любыми таблице-возвращающими функциями (table-valued functions). SCHEMATA - Выводит по одной строке для каждой базы данных. TABLE_CONSTRAINTS - Выводит по одной строке для каждого ограничения, определенного в текущей базе данных. TABLE_PRIVILEGES - Выводит по одной строке для каждой привилегии уровня таблицы, предоставленной текущему пользователю. TABLES - Выводит по одной строке для каждой таблицы или представления в текущей базе данных. VIEW_COLUMN_USAGE - Выводит по одной строке для каждого столбца в представлении, включая базовые таблицы этого столбца, когда это возможно. VIEW_TABLE_USAGE - Выводит по одной строке для каждой таблицы, используемой в представлении. VIEWS - Выводит по одной строке для каждого представления. В Books Online каждое из этих представлений описано более детально, включая полное описание возвращаемых ими результатов.

Функции метаданных

SQL Server также имеет ряд функций, которые возвращают информацию об объектах в базе данных. В качестве наглядного примера, рассмотрим использование функции ColumnProperty. Выполним следующий запрос в базу данных Northwind:

SELECT COLUMNPROPERTY( OBJECT_ID('Categories'),'CategoryID','IsIdentity')

В результате исполнения этого запроса возвращается 1, которая указывает, что столбец CategoryID является Identity. Существуют также другие, дополнительные функции, которые призваны выдавать информацию об Identity столбцах. С помощью функции ColumnProperty можно проверять довольно большой набор свойств, включая: допустимость null-значений, точность, масштаб и т.д. Многие из этих свойств также доступны и представлениях информационной схемы, но некоторых там нет. В Books Online имеется полный список функций метаданных.
В представленном выше запросе, Мы использовали ещё одну функцию метаданных - OBJECT_ID. Многие из функций метаданных принимают в качестве указателя на объект только его идентификатор (ID). В таких случаях, удобно использование этой функции, которая как раз и возвращает необходимый ID объекта, указанного в её параметре по имени. Обратной для этой функции является функция OBJECT_NAME, которая возвращает по ID объекта его имя.
Другая удобная функция - ObjectProperty, которая работает аналогично ColumnProperty, но имеет намного больше свойств, которые можно проверить. Например, следующий запрос покажет таблицы, которые имеют Identity, кластерные индексы и первичные ключи.

SELECT  TABLE_NAME, 
  IDNTY = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasIdentity'),
  CLSTRD = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasClustIndex'),
  PK = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasPrimaryKey')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'

Так Вы можете делать проверку того, действительно ли первичный ключ является кластерным индексом. Этот способ удобен для проектов, в которых разработчики могут создавать собственные таблицы. Дополнительные функции включают: IndexProperty, DatabaseProperty, FileGroupProperty, FullTextProperty и некоторые другие, хорошо описанные в Books Online.

[В начало]