14. Метаданные и доступ к ним. - KattyOG/Database GitHub Wiki

Метаданные и доступ к ним.

В основном справочный материал

Метаданные, в общем случае, это данные о данных, информация об информации, описание контента. Каждая СУБД сохраняет метаданные обо всех сущностях базы данных.

В разных СУБД применяются разные названия для метаданных - системный каталог, словарь данных и др. Однако общим свойством всех современных реляционных СУБД является то, что каталог/словарь сам состоит из таблиц, а точнее - системных таблиц. В результате пользователь может обращаться к метаданным так же, как и к прикладным данным, используя инструкцию SELECT. Изменения же в каталоге/словаре производятся автоматически при выполнении пользователем инструкций, изменяющих состояние объектов базы данных. Системные таблицы не должны изменяться непосредственно ни одним пользователем. Например, не стоит изменять системные таблицы с помощью инструкций DELETE, UPDATE или INSERT либо с помощью пользовательских триггеров. Обращение к документированным столбцам системных таблиц разрешено. Однако многие столбцы системных таблиц не документированы. В приложениях непосредственные запросы к недокументированным столбцам применять не следует. Чтобы исключить прямой доступ к системным таблицам, пользователь «видит» не сами таблицы, а созданные на их базе представления, которые он, конечно же, не может изменять. Состав и структура каталога/словаря очень различны для различных СУБД. Ограничимся рассмотрением метаданных и способов доступа к ним на примере СУБД MS SQL Server.

Microsoft SQL Server предоставляет следующие коллекции системных представлений, содержащие метаданные:

  • Представления информационной схемы
  • Представления каталога
  • Представления совместимости
  • Представления репликации
  • Динамические административные представления и функции
  • Представления приложения уровня данных (DAC)

Представления информационной схемы определяются в особой схеме с именем INFORMATION_SCHEMA. Эта схема содержится в любой базе данных и состоит из 20 представлений:

  • CHECK_CONSTRAINTS
  • REFERENTIAL_CONSTRAINTS
  • COLUMN_DOMAIN_USAGE
  • ROUTINES
  • COLUMN_PRIVILEGES
  • ROUTINE_COLUMNS
  • COLUMNS
  • SCHEMATA
  • CONSTRAINT_COLUMN_USAGE
  • TABLE_CONSTRAINTS
  • CONSTRAINT_TABLE_USAGE
  • TABLE_PRIVILEGES
  • DOMAIN_CONSTRAINTS
  • TABLES
  • DOMAINS
  • VIEW_COLUMN_USAGE
  • KEY_COLUMN_USAGE
  • VIEW_TABLE_USAGE
  • PARAMETERS
  • VIEWS

Каждое представление информационной схемы содержит метаданные для объектов, хранящихся в этой конкретной базе данных. Представления информационной схемы, включенные в SQL Server, соответствуют стандартному определению ISO для INFORMATION_SCHEMA. При ссылке на представления информационной схемы необходимо использовать полное имя, включающее имя схемы INFORMATION_SCHEMA. Например:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Products';
GO

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

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

Представления каталога в SQL Server организованы в следующие 25 категории:

  • Представления каталога групп доступности AlwaysOn
  • Представления каталога связанных серверов
  • Представления каталога отслеживания изменений
  • Представления каталога сообщений (для ошибок)
  • Представления каталога сборки среды CLR
  • Представления каталога объектов
  • Представления каталога баз данных и файлов
  • Представления каталога функции секционирования
  • Представления компонента Database Mail
  • Представления управления на основе политик
  • Представления каталога зеркального отображения базы данных
  • Представления каталога регулятора ресурсов
  • Представления сборщика данных
  • Представления каталога скалярных типов
  • Пространства данных
  • Представления каталога схем
  • Представления каталога конечных точек
  • Представления каталога безопасности
  • Представления каталога расширенных событий
  • Представления каталога компонента Service Broker
  • Представления каталога расширенных свойств
  • Представления каталога конфигурации уровня сервера
  • Представления каталога FileTable
  • Представления каталога схем XML (система типов XML)
  • Представления каталога полнотекстового и семантического поиска

Представления каталогов баз данных и файлов содержит следующие 6 представления:

  • sys.backup_devices
  • sys.database_files
  • sys.database_mirroring
  • sys.database_recovery_status
  • sys.databases
  • sys.master_files sys.databases - содержит одну строку для каждой базы данных в экземпляре Microsoft SQL Server. Чтобы проверить существование базы данных можно воспользоваться предикатом EXISTS и запросом на выборку из представления sys.databases. Следующий пример проверяет существование указанной базы данных. Если база данных существует, то она удаляется. Если базы данных не существует, то инструкция DROP DATABASE не выполняется.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FolktaleDB')
    DROP DATABASE [FolktaleDB]
GO

Представления каталога объектов содержит следующие 36 представления:

  • sys.allocation_units
  • sys.parameters
  • sys.assembly_modules
  • sys.partitions
  • sys.check_constraints
  • sys.procedures
  • sys.columns
  • sys.sequences
  • sys.computed_columns
  • sys.service_queues
  • sys.default_constraints
  • sys.sql_dependencies
  • sys.events
  • sys.sql_expression_dependencies
  • sys.event_notifications
  • sys.sql_modules
  • sys.extended_procedures
  • sys.stats
  • sys.foreign_key_columns
  • sys.stats_columns
  • sys.foreign_keys
  • sys.synonyms
  • sys.function_order_columns
  • sys.table_types
  • sys.identity_columns
  • sys.tables
  • sys.index_columns
  • sys.trigger_event_types
  • sys.indexes
  • sys.trigger_events
  • sys.key_constraints
  • sys.triggers
  • sys. numbered_procedure_parameters
  • sys.views
  • sys. numbered_procedures
  • sys.objects

sys.objects содержит одну строку для каждого определенного пользователем объекта в области схемы, который создан в базе данных. Представление sys.objects не показывает триггеры DDL, так как они не принадлежат области схемы. Все триггеры (как DML, так и DDL) размещены в представлении sys.triggers. Чтобы проверить существование объекта можно воспользоваться предикатом EXISTS и запросом на выборку из представления sys.objects. Аналогичный результат можно получить, если воспользоваться системной функцией OBJECT_ID. Возвращает идентификационный номер объекта базы данных для объекта области схемы. Следующий пример проверяет существование указанной таблицы, проверяя наличие у таблицы идентификатора объекта. Если таблица существует, то она удаляется. Если таблица не существует, то инструкция DROP TABLE не выполняется.

USE Northwind;
GO
IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL
     DROP TABLE dbo.Products;
GO

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

SELECT OBJECT_NAME(object_id) AS [constraint_name]
    ,SCHEMA_NAME(schema_id) AS [schema_name]
    ,OBJECT_NAME(parent_object_id) AS [table_name]
    ,type_desc
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' AND parent_object_id = OBJECT_ID('dbo.Products');
GO

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

Следующие скалярные функции возвращают информацию о базе данных и объектах баз данных:

@@PROCID fn_listextendedproperty

ASSEMBLYPROPERTY FULLTEXTCATALOGPROPERTY

COL_LENGTH FULLTEXTSERVICEPROPERTY

COL_NAME INDEX_COL

COLUMNPROPERTY INDEXKEY_PROPERTY

DATABASEPROPERTY INDEXPROPERTY

DATABASEPROPERTYEX OBJECT_ID

DB_ID OBJECT_NAME

DB_NAME OBJECTPROPERTY

FILE_ID OBJECTPROPERTYEX

FILE_IDEX SCHEMA_ID

FILE_NAME SCHEMA_NAME

FILEGROUP_ID SQL_VARIANT_PROPERTY

FILEGROUP_NAME TYPE_ID

FILEGROUPPROPERTY TYPE_NAME

FILEPROPERTY TYPEPROPERTY

Хранимые процедуры каталога

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

sp_column_privileges

sp_special_columns

sp_columns

sp_sproc_columns

sp_databases

sp_statistics

sp_fkeys

sp_stored_procedures

sp_pkeys

sp_table_privileges

sp_server_info

sp_tables