Эта статья является частью серии Stairway: Путеводитель для изучения метаданных базы данных

В дополнение к данным, которые хранятся нашими клиентами и пользователями в базе данных, существует огромное количество метаданных, «данные о данных», которые описывают, как создается, настраивается база данных и каковы ее свойства для разных объектов.  Этот путеводитель направлен на прояснение и объяснение того, как вы можете запрашивать и использовать эти метаданные для более эффективного управления базами данных SQL Server.

Этот путеводитель предназначен для обращения к широкому спектру опыта и знаний в SQL Server. Некоторые из них вам кажутся очевидными, но мы хотели бы добавить информацию, которая будет новой для даже опытных администраторов баз данных или разработчиков баз данных. В конце концов, часть информации была неожиданной. Однако мы будем принимать разумное рабочее знание об объектах базы данных и других типов метаданных; другими словами, что такое View, Table, Function, Procedure, Schema, constraint и т. д. на самом деле. После некоторой вводной теории мы постараемся как можно лучше проиллюстрировать практический и полезный код.

Что такое динамический онлайн-каталог?

Каждая система реляционных баз данных, такая как SQL Server, должна предоставлять информацию о своей структуре через тот же синтаксис SQL, который используется для доступа к данным. Обычно это достигается путем хранения определения структуры в специальных системных таблицах. Это означает, что в таблицах базы данных есть два типа таблиц, которые содержат «рабочие» данные и системные таблицы или представления, содержащие метаданные. Начиная с SQL Server 2005, никакие системные базовые таблицы и только представления не отображаются пользователю, и их можно увидеть в SSMS в браузере объектов.

На рисунке 1 показаны некоторые из этих информационных схем и системных представлений.

Рисунок 1: Некоторые из системных представлений

Сбор системных таблиц / представлений обычно упоминается в литературе по теории реляционных баз данных как системный каталог или словарь данных.

Это кажется рекурсивным, но глубоко внутри SQL Server существуют системные таблицы, которые используются для отслеживания всех процессов в наших базах данных. Эти системные таблицы хранят информацию о таких составляющих базы данных, как таблицы, подпрограммы, столбцы и индексы. Все это сделано для соответствия одному из 13 правил Эдгара Кодда для реализации реляционной базы данных. Это правило определяет динамический онлайн-каталог, который является данными о данных, иначе известный как «метаданные».

Правило Эдгара Кодда 4, «Динамический онлайн-каталог, основанный на реляционной модели» гласит:

«Описание базы данных представлено на логическом уровне точно так же, как и обычные данные, так что авторизованные пользователи могут применять к опросу тот же реляционный язык, что и к обычным данным».

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

Как мы получаем информацию?

Поскольку мы не можем напрямую обращаться к динамическому онлайн-каталогу, мы используем представления и функции для просмотра информации в нем. Вы можете видеть только информацию, которую вы имеете право видеть. Вы также не можете напрямую изменять данные в каталоге, потому что у вас есть лучший подход к использованию языка определения данных (DDL) в пользовательской базе данных. Эти SQL DDL-операторы включают инструкции CREATE, DROP, ALTER, GRANT, DENY, REVOKE и sp_rename для внесения изменений. Всегда есть способ, используя DDL, изменять любую информацию в представлениях каталога, даже если это не всегда очевидно.

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

К сожалению, эта схема не всегда обеспечивает достаточную информацию для наших целей, потому что поставщики РСУБД не уделяют пристальное внимание тому, чтобы соответствовать стандарту. Это означает, что у нас нет выбора, кроме как дополнить эту Информационную схему, используя представления и функции системного каталога SQL Server

Этот путеводитель будет ориентирован на задачи, чтобы узнать, что находится в ваших базах данных. Однако, прежде чем мы начнем с практических деталей, нужно будет объяснить несколько терминов и методик в мельчайших деталях, которые достаточно для того, чтобы облегчить вам примеры.

На рисунке 2 показаны интерфейсы, которые предоставляются для доступа к метаданным, хранящимся в SQL Server. Затем мы опишем каждый из этих терминов

Рисунок 2: Интерфейсы, используемые для доступа к метаданным SQL Server

Системные представления

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

Информационная схема представляет собой набор представлений с информацией об объектах, находящихся в текущей базе данных. Эта схема существует во всех базах данных. Вы можете видеть только информацию об объектах, которые вы имеете право просматривать в текущей базе данных. Информационная схема является довольно стандартной для РСУБД, поскольку она определена в стандарте SQL. Ваш код, который использует Информационную схему, скорее всего, будет переносимым во всех основных РСУБД. Представления информационной схемы в настоящее время не содержат информацию, специфичную для реализации SQL Server, хотя стандарт позволяет это сделать.

Информационная схема особенно важна для процессов, которые должны работать на разных РСУБД. Они отлично подходят для повседневной работы, такие как проверка наличия таблицы перед ее доступом, но они ограничены по своей стоимости, когда вам нужны подробные отчеты о вашей базе данных. Они также используют несколько другую стандартную номенклатуру: база данных, например, называется каталогом, а определяемый пользователем тип данных называется «доменом».

В MSDN есть пугающее сообщение с этим предупреждением: «Не используйте представления INFORMATION_SCHEMA для определения схемы объекта». Это сообщение не означает, что информация схемы может быть неправильной, но это относится к практике попыток использовать эти представления для разработки схемы, зная только имя таблицы. SQL Server позволяет иметь одно и то же имя таблицы в разных схемах. Несмотря на это сообщение, Информационная схема отлично используется.

Представления совместимости (The Compatibility Views)

Представления совместимости поддерживают представление метаданных, которые были доступны в виде системных таблиц до SQL Server 2005 и предназначены только для обратной совместимости. Любые запросы этих системных таблиц все равно будут выполняться, но ничего из того, что было внесено в SQL Server 2005 и в дальнейшие версии, не было представлено, например разделы таблиц, поэтому только часть ваших метаданных или ее характеристики могут быть видны вам. Для больших баз данных с большим количеством пользователей, групп и ролей или типов данных, разрешенных SQL Server 2000, они потенциально опасны для использования, поскольку столбцы представления совместимости, в которых хранятся идентификаторы пользователей и идентификаторы типов, могут возвращать NULL или триггерные арифметические переполнения.

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

Представления каталога предоставляют нам информацию о структуре базы данных. Они также используются самим СУБД SQL Server, особенно в оптимизаторе запросов, поэтому они являются наиболее эффективным способом получения информации о метаданных. Все метаданные отображаются через эти представления в каталоге, за исключением репликации, резервного копирования, плана обслуживания базы данных или для данных каталога агента сервера SQL Server.

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

Не все в метаданных SQL Server является объектом. Например, статистика столбца, индекса или распределения не является объектом. Некоторые составляющие, такие как ограничения первичного ключа или расширенные свойства, имеют странный период полураспада, поскольку они представлены как объект, но создаются с помощью индекса, который принудительно вводит ключ, но который не является объектом. Некоторые объекты, в основном ограничения, имеют отношение “родительский / дочерний объект” к другому типу объекта: таблица.

Представления приложений уровня данных

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

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

Динамические представления и функции управления (DMV) используются для настройки производительности, диагностики проблем и мониторинга работоспособности сервера или базы данных. Они не предназначены для доступа к метаданным или смежной информации, но в контексте этого путеводителя они ценны для ознакомления, поскольку они дают информацию о том, как используется SQL Server. Это позволяет нам, например, не только перечислять индексы, но и перечислять их в порядке объема использования, которое они получают.

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

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

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

Существует несколько хранимых процедур, основная функция которых заключается в предоставлении информации метаданных для драйвера ODBC для SQL Server. Когда вы создаете соединение ODBC, эта информация становится доступной в виде набора объектов данных. Тем не менее, информация доступна в целом и может использоваться из SQL так же, как и любая другая хранимая процедура. Они обычно считаются менее полезными, чем представления каталога, потому что результат, возвращаемый хранимой процедурой, должен быть явно вставлен в таблицу или таблицу с использованием синтаксиса INSERT ... EXECUTE. Они также имеют только подмножество информации, доступной в представлении каталога.

Почему важны представления и функции метаданных?

Представления и функции метаданных позволяют вам:

• искать объекты метаданных;
• предоставлять вам отчеты и сводки о вашей базе данных;
• определять, кто имеет разрешение на просмотр или изменение данных;
• сократить повторяющийся набор текста;
• получить почти всю информацию, спрятанную где-то внутри SQL Server Management  Studio;
• сделать сценарии развертывания более безопасными и надежными;
• узнать, что недавно было изменено или создано;
• быстро выполнять работу над рядом функций или процедур;
• определять версии зарегистрированной базы данных;
• проверять код базы данных для практики кодирования, обнаружения повторяющихся индексов;
• сократить неэффективные операции point ‘n click.

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

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

Как мне начать?

Первым этапом обучения использованию представлений и функций метаданных является сбор запросов, которые используют их из множества авторитетных источников, таких как SQL Server Central, Simple-Talk или MSDN. Храните эти запросы, используя инструмент для ведения заметок. Это помогает, если это инструмент для хранения заметок или фрагментов, который позволяет легко и легко получить ответы на запросы, где бы вы ни находились. Через некоторое время вы немного измените их для своих конкретных требований. Затем вместо поиска списка таблиц в панели браузера объектов вы сможете просто получить соответствующий запрос из своей коллекции, выполнить его и быстро получить информацию.

Обычные примеры использования

Во всех этих примерах мы протестировали их на SQL Server 2008 и 2012. Там, где пример совместим только с более поздними версиями, будет упомянуто об этом.

Где расположен мой материал?

В представлении каталога sys.objects содержится полезная информация обо всех битах и частях базы данных, таких как таблицы, представления, триггеры, функции и процедуры. Некоторые из этих «объектов» требуют дополнительных данных, поэтому предусмотрены представления, которые наследуют от sys.object, но которые при этом предоставляют дополнительную информацию, относящуюся к этому типу объекта. Это относится к синонимам, внешним ключам, ограничениям проверки, ограничениям ключа, ограничениям по умолчанию, очередям службы, таблицам, представлениям и процедурам. К ним можно обратиться в каталогах sys.synonyms, sys.foreign_keys, sys.check_constraints, sys.key_constraints, sys.default_constraints, sys.service_queues, sys.tables, sys.views и sys.procedures ..

На рисунке 3 показаны все представления, наследующие столбцы sys.object. Это означает, что они имеют столбцы sys.objects и, кроме того, столбцы, соответствующие только их типу объекта. Это означает, что все эти представления имеют такую информацию, как create_date и modify_date, которые поступают из sys.objects.

Рисунок 3: Все представления каталога, зависящие от sys.objects

Чтобы перечислить все представления в вашей базе данных, просто сделайте это ...

 SELECT  object_schema_name(object_id)+'.'+name FROM sys.views;

 Или для перечисления всех ваших хранимых процедур ...

 SELECT  object_schema_name(object_id)+'.'+name FROM sys.procedures;

… или ваших внешних ключей

/* The Tables */
  --all user tables in the database
    SELECT
      ob.name AS User_Table, Coalesce(ep.value, '') AS documentation
    FROM sys.objects AS ob
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = ob.object_id
           AND ep.class = 1
           AND ep.minor_id = 0
    WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1 


/* The Views */
  --all views in the database
  SELECT ob.name AS ViewName, Coalesce(ep.value, '') AS documentation
  FROM sys.objects ob  LEFT OUTER JOIN sys.extended_properties AS ep
      ON ep.major_id = ob.object_id
         AND ep.class = 1
         AND ep.minor_id = 0
  WHERE objectproperty(ob.object_id,'IsView')= 1 


/* The Check Constraints */
 --all the check constraints in the database
    SELECT
      objects.name AS Name_of_Check_Constraint,
      Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
	  Coalesce(ep.value,'') AS documentation
   FROM sys.objects
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = objects.object_id AND ep.class=1
  	    AND ep.name='MS_Description'--the microsoft convention
    WHERE ObjectProperty(objects.object_id, 'IsCheckCnst') = 1 

/* The Constraints */ 
SELECT
 --all the constraints in the database
  objects.name AS Name_of_Constraint, --see all constraints and parent table
  Lower(Replace(type_desc,'_',' ')),--the type of constraint
  Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(objects.object_id, 'IsConstraint') = 1;
 
/* The Defaults */
--all old-style defaults in the database
SELECT
  objects.name, --see all Sybase-style defaults
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(objects.object_id, 'IsDefault') = 1;
 
/* The Default Constraints */
--all default constraints in the database and their parent table
SELECT objects.name AS Name_of_Default_Constraint,--see all Default constraints and parent table
	  Coalesce(ep.value,'') AS documentation,
object_schema_name(objects.parent_object_id)+'.'+object_name(objects.parent_object_id) AS parent,
	  Coalesce(EP_parent.value,'') AS documentation
FROM sys.objects 
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' --the microsoft convention
  LEFT OUTER JOIN sys.extended_properties AS EP_parent
    ON ep.major_id = objects.parent_object_id
       AND ep.name = 'MS_Description' --the microsoft convention	  
 WHERE objectproperty(objects.object_id,'IsDefaultCnst')= 1;
 
/* The Executables */
--all executables (procedures, functions etc) in the database
SELECT
  oe.name AS Name_Of_Executable,
  Replace(Lower(oe.type_desc), '_', ' ') AS Type_Of_Executable,
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oe
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oe.object_id
       AND EP.name = 'MS_Description' --the microsoft convention	  
WHERE ObjectProperty(oe.object_id, 'IsExecuted') = 1;


/* The Extended Stored Procedures */ 
--all extended stored procedures in the database
SELECT
  oep.name AS Name_of_Extended_Procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oep
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oep.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(oep.object_id, 'IsExtendedProc') = 1;
 
/* The Inline Functions */ 
--all inline functions in the database
SELECT ilf.name AS Inline_function,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ilf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ilf.object_id
       AND EP.name = 'MS_Description' --the microsoft convention	  
 WHERE objectproperty(ilf.object_id,'IsInlineFunction')= 1;
 
/* The Primary Keys */ 
--all primary keys in the database and their parent table
SELECT
  pk.name AS Primary_key,
  Object_Schema_Name(pk.parent_object_id) + '.' + Object_Name(pk.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS KeyDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS pk
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = pk.object_id
       AND EP.name = 'MS_Description' --the microsoft convention	
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = pk.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' --the microsoft convention	
WHERE ObjectProperty(pk.object_id, 'IsPrimaryKey') = 1;

/* The Stored Procedures */
--all stored procedures in the database
SELECT
  sp.name AS Stored_procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sp
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sp.object_id
       AND EP.minor_id = 0
       AND EP.name = 'MS_Description' --the microsoft convention	  
WHERE ObjectProperty(sp.object_id, 'IsProcedure') = 1;
 
/* The Queues */ 
--all queues in the database
SELECT
  q.name AS QueueName, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS q
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = q.object_id
       AND EP.name = 'MS_Description' --the microsoft convention	  
WHERE ObjectProperty(q.object_id, 'IsQueue') = 1;
 
/* The Rules */ 
--all old-style rules in the database
SELECT
  ru.name AS RuleName, --old-fashioned sybase-style rule
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ru
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ru.object_id
       AND EP.name = 'MS_Description' --the microsoft convention	  
WHERE ObjectProperty(ru.object_id, 'IsRule') = 1;
 
/* The Scalar Functions */ 
--all scalar functions in the database
SELECT
  sf.name AS Scalar_function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sf.object_id
       AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(sf.object_id, 'IsScalarFunction') = 1;

/* The System Tables */ 
--all system tables in the database
SELECT
  st.name AS System_table, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS st
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = st.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(st.object_id, 'IsSystemTable') = 1;

--all tables, including system tables, in the database
SELECT
  at.name AS TableName, --includes system tables etc
  Lower(Replace(type_desc,'_',' ')),--the type of constraint
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS at
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = at.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(at.object_id, 'IsTable') = 1;
 
/* The TVFs*/ 
--all table-valued functions in the database
SELECT
  tvf.name AS Table_Valued_Function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS tvf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tvf.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(tvf.object_id, 'IsTableFunction') = 1;
 
--all triggers in the database and their parent table
SELECT
  tr.name AS TriggerName,
  Object_Schema_Name(tr.parent_object_id) + '.' + Object_Name(tr.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS TriggerDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS tr
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tr.object_id
       AND EP.name = 'MS_Description' --the microsoft convention	
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = tr.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' --the microsoft convention	
WHERE ObjectProperty(tr.object_id, 'IsTrigger') = 1;
 
/* The Unique Constraints */ 
--all unique constraints in the database and their parent table
SELECT uc.name AS Unique_constraint,--all unique constraints
object_schema_name(uc.parent_object_id)+'.'+object_name(uc.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS ConstraintDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS uc
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = uc.object_id
       AND EP.name = 'MS_Description' --the microsoft convention	
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = uc.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' --the microsoft convention	
WHERE objectproperty(uc.object_id,'IsUniqueCnst')= 1;

No Comments