Системный каталог

Работа с базами данных в .NET Framework --- SQL Server 2012 --- Системный каталог

Системный каталог состоит из таблиц, описывающих структуру объектов базы данных, таких как базовые таблицы, представления, индексы и собственно базы данных. Эти таблицы называются системными базовыми таблицами. Компонент Database Engine часто обращается к системному каталогу за информацией, необходимой для правильного функционирования системы.

Компонент Database Engine отличает системные базовые таблицы базы данных master от базовых таблиц пользовательских баз данных. Системные таблицы базы данных master принадлежат к системному каталогу, а системные таблицы определенной базы данных составляют каталог этой базы данных. Поэтому системные базовые таблицы присутствуют только в одном экземпляре для всей системы (если они принадлежат исключительно к базе данных master), тогда как другие таблицы присутствуют в одном экземпляре в каждой базе данных, включая базу данных master.

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

Для обращения за информацией к системным базовым таблицам можно использовать несколько разных интерфейсов:

Представления каталогов

Является основным интерфейсом для метаданных, хранящихся в системных базовых таблицах. Метаданные описывают атрибуты объектов в системе баз данных.

DMV и DMF

Динамические административные представления (Dynamic Management Views, DMV) и динамические административные функции (Dynamic Management Functions, DMF) обычно применяются для просмотра активных процессов и содержимого памяти.

Информационная схема

Стандартное решение для доступа к метаданным, которое предоставляет общий интерфейс не только для компонента Database Engine, но и для всех существующих реляционных систем баз данных (при условии, что конкретная система поддерживает информационную схему).

Системные функции и функции свойств

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

Системные хранимые процедуры

Некоторые системные хранимые процедуры можно использовать для получения доступа к содержимому системных баз данных и модификации этого содержимого.

Упрощенная форма системной информации компонента Database Engine и различные интерфейсы для доступа к ней показаны на рисунке ниже:

Все эти интерфейсы можно подразделить на две группы: общие интерфейсы (представления каталога, динамические административные представления и функции, информационная схема) и специализированные интерфейсы компонента Database Engine (системные хранимые процедуры, системные функции и функции свойств).

"Общие" означает, что все реляционные системы баз данных поддерживают такие интерфейсы, но используют другую терминологию. Например, в терминологии Oracle, представления каталога и динамические административные представления называются "представления словаря данных" и "представления V$" соответственно.

Общие интерфейсы

Как уже упоминалось, к общим относятся следующие интерфейсы:

• представления каталога;

• динамические административные представления и динамические административные функции;

• информационная схема.

Представления каталога

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

Представление каталога sys.objects возвращает строку для каждого объекта, определенного пользователем в схеме пользователя. Существуют два других представления каталога, которые предоставляют доступ к подобной информации: sys.system_objects и sys.all_objects. Представление sys.system_objects возвращает строку для каждого системного объекта, а представление sys.all_objects отображает объединение всех определенных пользователем и системных объектов в области видимости схемы. (Все три представления каталога имеют одинаковую структуру.)

Наиболее важные столбцы представления каталога sys.objects перечислены в таблице ниже:

Представление каталога sys.columns возвращает строку для каждого столбца объектов со столбцами, таких как таблицы и представления. Наиболее важные столбцы представления каталога sys.columns перечислены в таблице ниже:

Представление каталога sys.database_principals возвращает строку для каждого принципала системы безопасности (т.е. пользователя, группы или роли в базе данных).

В SQL Server 2012 продолжают поддерживаться так называемые представления совместимости с целью обеспечения обратной совместимости. Каждое представление совместимости имеет такое же имя и такую же структуру, как и соответствующая системная базовая таблица системы SQL Server 2000. Представления совместимости не предоставляют доступ к метаданным, связанным с возможностями, введенными, начиная с версии SQL Server 2005. Эти представления являются устаревшей возможностью и будут изъяты из будущих версий SQL Server.

Запросы к представлениям каталога

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

USE SampleDb;

SELECT object_id, principal_id, type
    FROM sys.objects
    WHERE name = 'Employee';

В этом примере происходит выборка идентификатора таблицы, идентификатора пользователя и типа таблицы для таблицы Employee. Результат исполнения этого запроса:

В столбце object_id представления каталога sys.objects отображается однозначный идентификационный номер соответствующего объекта базы данных. Значение NULL в столбце principal_id указывает, что владелец данного объекта тот же самый, что и владелец схемы. Значение и в столбце type означает пользовательскую таблицу (U - от англ. user, пользователь).

USE SampleDb;

-- Вернет имена таблиц Department и Project
SELECT sys.objects.name
    FROM sys.objects INNER JOIN sys.columns
    ON sys.objects.object_id = sys.columns.object_id
    WHERE sys.objects.type = 'U'
        AND sys.columns.name = 'Number';

В этом примере происходит выборка имен всех таблиц базы данных SampleDb, которые содержат столбец Number.

USE SampleDb;

-- Вернет 'dbo'
SELECT sys.database_principals.name
    FROM sys.database_principals 
        INNER JOIN sys.objects
    ON sys.database_principals.principal_id = sys.objects.schema_id
    WHERE sys.objects.name = 'Employee'
        AND sys.objects.type = 'U';

В этом примере мы определяем схему, которой принадлежит таблица Employee.

Динамические административные представления и функции

Динамические административные представления (Dynamic Management Views, DMV) и динамические административные функции (Dynamic Management Functions, DMF) возвращают информацию о состоянии сервера, которую можно применить для наблюдения над активными процессами и, следственно, для настройки производительности системы или для отслеживания действительного состояния системы. В отличие от представлений каталога, DMV и DMF основаны на внутренних структурах системы.

Основное различие между представлениями каталога и DMV состоит в их применении: представления каталога предоставляют информацию о метаданных, тогда как DMV и DMF применяются для доступа к динамическим свойствам системы. Иными словами, DMV применяются для получения информации о базе данных, об отдельных запросах или отдельных пользователях.

DMV и DMF принадлежат к схеме sys, а их имена состоят из префикса dm_ и текстовой строки, указывающей категорию, к которой принадлежит DMV или DMF.

В следующем списке перечислены некоторые из этих категорий и даны их краткие описания:

• sys.dm_db_* - возвращает информацию о базах данных и их объектах;

• sys.dm_tran_* - возвращает информацию, имеющую отношение к транзакциям;

• sys.dm_io_* - возвращает информацию о действиях по вводу/выводу;

• sys.dm_exec_* - возвращает информацию, связанную с исполнением пользовательского кода.

В каждой новой версии SQL Server корпорация Microsoft непрерывно увеличивает количество поддерживаемых DMV. Так SQL Server 2012 содержит 20 новых DMV, а их общее число равно 155.

В этом разделе рассматриваются два новых DMV. Представление sys.dm_exec_describe_first_result_set описывает первый результирующий набор группы результирующих наборов. Поэтому DMV можно использовать при объявлении нескольких последовательных запросов в пакете или хранимой процедуре.

USE SampleDb;

GO
CREATE PROC TwoSelects
    AS SELECT EmpId, Job 
        FROM Works_on 
        WHERE EmpId BETWEEN 1000 and 9999;

    SELECT Id, LastName FROM Employee WHERE LastName LIKE 'В%';

GO
SELECT is_hidden hidden ,column_ordinal ord,
    name, is_nullable nul, system_type_id id
    FROM sys.dm_exec_describe_first_result_set ('TwoSelects', NULL, 0) ;

Результат выполнения этого запроса:

Процедура, сохраненная в примере, содержит две инструкции SELECT, осуществляющих выборку из базы данных SampleDb. Последующий запрос отображает несколько свойств из результата первого запроса, используя для этого представление sys.dm_exec_describe_first_result_set.

Представление sys.dm_db_uncontained_entities отображает все неограниченные объекты, используемые в базе данных. Неограниченными объектами (uncontained objects) называются объекты, которые пересекают границы приложения в автономной базе данных.

Информационная схема

Информационная схема состоит из представлений, доступных только для чтения, которые предоставляют информацию обо всех таблицах, представлениях и столбцах компонента Database Engine, к которым имеется доступ. В отличие от системного каталога, который управляет метаданными применительно к системе, как к единому целому, информационная схема в основном управляет средой базы данных.

Информационная схема была впервые представлена в стандарте SQL92. Компонент Database Engine отображает представления информационной схемы, чтобы разработанные на других системах баз данных приложения смогли получить свой системный каталог, не используя его прямым образом. Эти стандартные представления используют разную терминологию, поэтому при использовании имен столбцов имейте в виду, что "каталог" является синонимом базы данных, а "домен" - синонимом пользовательского типа данных.

В следующих разделах описываются наиболее важные представления информационной схемы.

Представление information_schema.tables

Представление information_schema.tables возвращает одну строку для каждой таблицы в текущей базе данных, к которой пользователь имеет доступ. Это представление извлекает информацию из системного каталога, используя представление каталога sys.objects. В таблице ниже дано описание четырех столбцов этого представления:

Представление information_schema.columns

Представление information_schema.columns возвращает одну строку для каждого столбца, доступного текущему пользователю в текущей базе данных. Это представление извлекает информацию из системного каталога, используя представления каталога sys.columns и sys.objects. В таблице ниже приведено описание шести наиболее важных столбцов этого представления:

Специализированные интерфейсы

В предшествующем разделе мы рассмотрели получение доступа к системным базовым таблицам, используя общие интерфейсы. Но системную информацию можно также получить, используя один из следующих специализированных механизмов компонента Database Engine:

• системные хранимые процедуры;

• системные функции;

• функции свойств.

Эти интерфейсы рассматриваются в последующих подразделах.

Системные хранимые процедуры

Системные хранимые процедуры применяются для выполнения многих административных и пользовательских задач, таких как переименование объектов базы данных, идентификация пользователей и мониторинг авторизации и ресурсов. Для извлечения и модифицирования системной информации почти все существующие системные хранимые процедуры используют системные базовые таблицы.

Наиболее важным свойством системных хранимых процедур является предоставляемая ими возможность легко и надежно модифицировать системные базовые таблицы. В этом разделе рассматриваются две системные хранимые процедуры: sp_help и sp_configure. Некоторые из системных хранимых процедур обсуждались в предыдущих статьях с соответствующей тематикой.

Системная процедура sp_help возвращает сведения об объектах базы данных. Параметром для этой процедуры может служить имя любого объекта базы данных или типа данных. При выполнении процедуры sp_help без параметра она возвращает информацию обо всех объектах текущей базы данных.

Хранимая системная процедура sp_configure возвращает или изменяет глобальные конфигурационные параметры текущего сервера. В примере ниже показано использование системной процедуры sp_configure:

USE SampleDb;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'fill factor', 100;
RECONFIGURE WITH OVERRIDE;

Обычно, доступ к дополнительным конфигурационным опциям SQL Server не разрешен. Поэтому первая инструкция EXECUTE в примере дает указание системе разрешить изменения дополнительных параметров. Следующая инструкция, RECONFIGURE WITH OVERRIDE, устанавливает это разрешение. Теперь можно изменять значения дополнительных параметров. В примере параметру fill factor присваивается значение 100 (инструкция EXEC), после чего выполняется установка этого изменения (инструкция RECONFIGURE). (Параметр fill factor указывает объем хранилища в процентах для страниц индексов.)

Системные функции

Системные функции можно использовать для доступа к базовым таблицам. В примере ниже показано использование инструкции SELECT для выборки одной и той же информации, используя два разных интерфейса:

USE SampleDb;

SELECT object_id
    FROM sys.objects
    WHERE name = 'Employee';

SELECT object_id('Employee');

В этом примере вторая инструкция SELECT возвращает идентификатор таблицы Employee, используя системную функцию object_id. (Полученную информацию можно сохранить в переменной и применить в качестве параметра при вызове команды или системной хранимой процедуры.)

Далее приводится список некоторых системных функций для доступа к системным базовым таблицам. Назначение этих функций должно быть понятно с их названий:

• OBJECT_ID (object_name)

• OBJECT_NAME (object_id)

• USER_ID ([user_name])

• USER_NAME ([user_id])

• DB_ID ([ db_name])

• DB_NAME ([db_id])

Функции свойств

Функции свойств возвращают свойства объектов баз данных, типов данных и файлов. Обычно функции свойств могут возвратить больше информации, чем системные функции, т.к. функции свойств поддерживают десятки свойств (в виде параметров), которые можно явно указывать.

Почти все функции свойств возвращают одно из следующих значений: 0, 1 или NULL. Возвращение функцией свойств значения 0 означает, что объект не обладает данным свойством. Возвращение функцией свойств значения 1 означает, что объект обладает данным свойством. А возвращение значения NULL означает, что системе неизвестно, обладает ли данный объект указанным свойством.

Компонент Database Engine поддерживает, среди прочих, следующие функции свойств:

• OBJECTPROPERTY (id, property)

• COLUMNPROPERTY (id, column, property)

• FILEPROPERTY (filename, property)

• TYPEPROPERTY (type, property)

Функция objectProperty возвращает информацию об объектах текущей базы данных. Функция columnProperty возвращает информацию о столбце или о параметре процедуры. Функция fileProperty возвращает значение указанного свойства для заданного имени файла. Функция typeProperty возвращает информацию о типе данных. (Описание поддерживаемых свойств для всех функций свойств смотрите в электронной документации.)