Представления информационной схемы Microsoft SQL Server

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

Содержание

Что такое представления информационной схемы в SQL Server?
Создаем исходные данные для примеров
Пример 1. Получаем список всех хранимых процедур в MS SQL Server
Пример 2. Получаем список всех представлений в БД Microsoft SQL Server
Пример 3. Получаем список и описание всех параметров хранимой процедуры в SQL сервере

Что такое представления информационной схемы в SQL Server?

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

INFORMATION_SCHEMA есть в каждой базе данных Microsoft SQL Server. Любое представление данной схемы содержит метаданные для всех хранящихся объектов в конкретной базе данных.

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

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

INFORMATION_SCHEMA.название представления.

Заметка! Представление (VIEW) в T-SQL – что это такое?

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

SCHEMATA – представление, с помощью которого можно получить все схемы базы данных;
TABLES – представление возвращает список всех таблиц и представлений в текущей базе данных. Содержит всего несколько столбцов: БД, схему, имя таблицы (или представления) и тип объекта («VIEW» — представление и «BASE TABLE» — таблица);
TABLE_CONSTRAINTS – с помощью данного представления можно получить сведения об ограничениях таблицы, таких как: CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY;
TABLE_PRIVILEGES – возвращает перечень прав доступа к таблице, которые предоставляются текущему пользователю;
VIEWS — представление, предназначенное для получения списка всех представлений в базе данных. Содержит несколько колонок, например, такие как БД, схему, имя представления, а также очень интересную колонку VIEW_DEFINITION – это SQL запрос, с помощью которого было создано это представление (только если это определение не более 4000 символов), также можно получить исходную инструкцию функцией OBJECT_DEFINITION(object_id);
VIEW_TABLE_USAGE — представление, с помощью которого можно получить список всех таблиц, которые используются в представлении. Иными словами, если Вы в определении предоставления используете несколько таблиц (например, объединяете их), то данное представление вернет Вам перечень этих таблиц;
VIEW_COLUMN_USAGE — с помощью этого представления можно получить список всех столбцов, которые есть в представлении, включая имя базовой таблицы;
COLUMNS – возвращает список столбцов таблицы. Содержит много полезной информации, например, такой как: тип данных столбца, идентификационный номер столбца, может ли столбец принимать значение NULL и т.д.;
COLUMN_PRIVILEGES – представление возвращает список прав доступа к колонке в таблице, если такие права назначались;
ROUTINES — с помощью данного представления можно получить список всех хранимых процедур и функций доступных пользователю. Данное представление возвращает много данных, включая столбец ROUTINE_DEFINITION, который содержит сведения об исходной SQL инструкции создавшей данную функцию или процедуру (только если это определение не более 4000 символов). Исходный SQL запрос можно также узнать с помощью функции OBJECT_DEFINITION(object_id);
ROUTINE_COLUMNS — возвращает список столбцов табличной функции. Другими словами, если у Вас в БД есть функции, которые возвращают таблицу, т.е. табличные функции, то с помощью этого представления Вы можете посмотреть, какие столбцы возвращают эти функции, включая сведения о типе данных, идентификационном номере и так далее;
PARAMETERS – возвращает перечень параметров хранимых процедур или пользовательских функций, включая краткое их описание (тип данных и т.д.);
DOMAINS – возвращает список псевдонимов типов данных;
COLUMN_DOMAIN_USAGE – представление можно использовать, для того чтобы получить список всех столбцов, которые используют псевдонимы типов данных;
DOMAIN_CONSTRAINTS – с помощью этого представления можно получить список псевдонимов типов данных, к которым привязано правило;
CHECK_CONSTRAINTS – представление возвращает список проверочных ограничений. Есть столбец CHECK_CLAUSE, который показывает текст инструкции ограничения;
REFERENTIAL_CONSTRAINTS – данное представление возвращает список ограничений FOREIGN KEY. Есть полезные столбцы, связанные со сведениями о правилах обновления и удаления, столбцы UPDATE_RULE (правило ON UPDATE) и DELETE_RULE (правило ON DELETE);
CONSTRAINT_COLUMN_USAGE – возвращает список столбцов, для которых определено ограничение, включая имя ограничения;
CONSTRAINT_TABLE_USAGE – возвращает список таблиц, в которых есть ограничения, включая имя ограничения;
KEY_COLUMN_USAGE – возвращает столбцы, которые ограничены как ключ.

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

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Создаем исходные данные для примеров

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

Примечание! Все действия нижу, будут выполнены в СУБД Microsoft SQL Server 2016 Express.

Инструкция создания таблицы

   
   CREATE TABLE TestTable(
           ProductId INT IDENTITY(1,1) NOT NULL,
           ProductName VARCHAR(100) NOT NULL,
           Price MONEY
   ) 
   GO

Инструкция добавления данных

   
   INSERT INTO TestTable (ProductName, Price)
           VALUES ('Системный блок', 600),
                   ('Монитор', 400),
                   ('Клавиатура', 120),
                   ('Принтер', 450),
                   ('Сканер', 300),
                   ('Мышь', 60),
                   ('Телефон', 350)
   GO

Инструкция создания представления

   
   CREATE VIEW TestView
   AS 
           SELECT ProductId, ProductName, Price
           FROM TestTable
   GO

Инструкция создания процедуры

   
   CREATE PROCEDURE TestProcedure (@ProductId INT = NULL, 
                                                   @Price MONEY = NULL)
   AS
   BEGIN
        SELECT ProductId, ProductName, Price
        FROM TestTable
        WHERE ProductId = COALESCE(@ProductId, ProductId)
          AND Price = COALESCE(@Price, Price)
   END

Теперь можем переходить к рассмотрению примеров.

Пример 1. Получаем список всех хранимых процедур в MS SQL Server

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

   
   SELECT ROUTINE_NAME AS [Имя процедуры], 
           CREATED AS [Дата создания], 
           ROUTINE_DEFINITION AS [Инструкция создания]
   FROM INFORMATION_SCHEMA.ROUTINES
   WHERE ROUTINE_TYPE = 'PROCEDURE'
   ORDER BY CREATED DESC

В данном случае мы обратились к представлению ROUTINES информационной схемы. В качестве альтернативы можно использовать и системное представление sys.procedures, но только в этом случае для определения исходной SQL инструкции создания процедуры, нам нужно будет использовать функцию OBJECT_DEFINITION, так как в этом представлении такой информации нет.

   
   SELECT name AS [Имя процедуры], 
           create_date AS [Дата создания], 
           OBJECT_DEFINITION(object_id) AS [Инструкция создания]
   FROM sys.procedures
   ORDER BY create_date DESC

Пример 2. Получаем список всех представлений в БД Microsoft SQL Server

Если Вам нужно получить список всех представлений, то можно использовать представление информационной схемы VIEWS.

   
   SELECT TABLE_NAME AS [Имя представления], 
           VIEW_DEFINITION AS [Инструкция создания] 
   FROM INFORMATION_SCHEMA.VIEWS

Если Вам нужно больше информации о представлениях, то существует другой вариант получить данный список, например, системное представление sys.views (исходной SQL инструкции там также нет, но есть дата создания).

   
   SELECT name AS [Имя представления],
           create_date AS [Дата создания], 
           OBJECT_DEFINITION(object_id) AS [Инструкция создания]
   FROM sys.views
   ORDER BY create_date DESC

Пример 3. Получаем список и описание всех параметров хранимой процедуры в SQL сервере

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

   
   SELECT SPECIFIC_NAME AS [Имя процедуры],
           ORDINAL_POSITION AS [Порядковый номер параметра],
           PARAMETER_NAME AS [Имя параметра],
           DATA_TYPE AS [Тип данных параметра]
   FROM INFORMATION_SCHEMA.PARAMETERS
   WHERE SPECIFIC_NAME = 'TestProcedure'

В данном случае, также можно использовать альтернативный вариант, а именно системное представление sys.parameters, только в данном случае потребуется дополнительное объединение с системным представлением sys.types и вызов функции OBJECT_NAME.

   
   SELECT OBJECT_NAME(object_id) AS [Имя процедуры],
           P.parameter_id AS [Порядковый номер параметра],
           P.name AS [Имя параметра],
           T.name AS [Тип данных параметра]
   FROM sys.parameters P
   LEFT JOIN sys.types T ON P.user_type_id = T.user_type_id
   WHERE OBJECT_ID = OBJECT_ID('TestProcedure')

На этом у меня все, надеюсь, статья была Вам полезной, всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, пока!