Fn listextendedproperty: примеры (SQL)

Короткие примеры использования

Получение всех расширенных свойств в текущей базе данных.

SELECT * FROM fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL);

objtype | objname | name | value
--------|---------|------|-------
SCHEMA  | dbo     | MS_Description | Основная схема

Получение свойств для конкретной таблицы.

SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE', 'Employees', NULL, NULL);

objtype | objname   | name           | value
--------|-----------|----------------|-------
TABLE   | Employees | MS_Description | Таблица сотрудников

Поиск свойства с определенным именем для столбца.

SELECT * FROM fn_listextendedproperty('Описание', 'SCHEMA', 'dbo', 'TABLE', 'Employees', 'COLUMN', 'LastName');

objtype | objname   | name       | value
--------|-----------|------------|-------
COLUMN  | LastName  | Описание   | Фамилия сотрудника

Похожие функции в MS SQL Server

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

Прямой запрос к системному представлению sys.extended_properties предоставляет полный контроль и возможность сложных соединений с другими каталогами. Хранимая процедура sp_addextendedproperty и связанные с ней процедуры (sp_updateextendedproperty, sp_dropextendedproperty) используются для управления свойствами.

Для простых сценариев выборки fn_listextendedproperty более удобна благодаря понятной иерархической фильтрации. Для сложных запросов или массовых операций предпочтительнее прямое обращение к sys.extended_properties.

Аналоги в других СУБД и языках

Концепция расширенных свойств или комментариев реализована в различных СУБД по-разному.

MySQL: Использует конструкцию COMMENT для таблиц и столбцов, которую можно получить из INFORMATION_SCHEMA.

SELECT COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';

COLUMN_COMMENT
--------------
Комментарий к столбцу

Oracle: Комментарии хранятся в представлениях USER_TAB_COMMENTS, USER_COL_COMMENTS.

SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME='EMPLOYEES';

COMMENTS
--------
Таблица сотрудников

PostgreSQL: Для хранения описаний используется системный каталог pg_description, а для получения — функция obj_description().

SELECT obj_description('public.employees'::regclass);

obj_description
---------------
Таблица сотрудников

SQLite: Прямой аналог отсутствует. Метаданные можно хранить только в пользовательских таблицах.

В отличие от MS SQL, в перечисленных системах обычно нет единой иерархической функции, аналогичной fn_listextendedproperty.

Распространенные ошибки

Типичной ошибкой является неверное указание иерархии объектов, приводящее к пустому результату.

-- Ошибка: Указан тип объекта 'TABLE', но не указана схема.
SELECT * FROM fn_listextendedproperty(NULL, NULL, NULL, 'TABLE', 'Employees', NULL, NULL);

(ни одной строки не возвращено)

Использование функции в контексте базы данных, отличной от той, где существуют свойства.

USE master;
SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE', 'Employees', NULL, NULL);
-- Свойства находятся в пользовательской базе, поэтому результат будет пустым.

Попытка фильтрации по несуществующему имени свойства или объекта также возвращает пустой набор.

Изменения в последних версиях

Функция fn_listextendedproperty была представлена в SQL Server 2005 и с тех пор ее сигнатура и поведение остаются стабильными. В последних версиях SQL Server (2016 и новее) не было внесено существенных изменений в работу этой функции. Поддержка сохраняется для обеспечения обратной совместимости.

Расширенные примеры применения

Получение всех свойств для столбцов определенной таблицы с приведением типа значения.

SELECT
    objname AS ColumnName,
    name AS PropertyName,
    CAST(value AS NVARCHAR(MAX)) AS PropertyValue
FROM fn_listextendedproperty(NULL, 'SCHEMA', 'dbo', 'TABLE', 'Orders', 'COLUMN', NULL)
ORDER BY objname;

ColumnName | PropertyName | PropertyValue
-----------|--------------|--------------
OrderID    | Описание     | Уникальный идентификатор
OrderDate  | MS_Description | Дата оформления

Использование функции в соединении с другими системными представлениями для формирования документации.

SELECT
    t.name AS TableName,
    c.name AS ColumnName,
    ep.value AS Description
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
OUTER APPLY fn_listextendedproperty('MS_Description', 'SCHEMA', SCHEMA_NAME(t.schema_id), 'TABLE', t.name, 'COLUMN', c.name) ep
WHERE t.is_ms_shipped = 0;

TableName | ColumnName | Description
----------|------------|------------
Employees| Email      | Адрес электронной почты
Products | Price      | Стоимость единицы товара

Поиск всех объектов, имеющих свойство с определенным значением.

SELECT objtype, objname, name, value
FROM fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE CAST(value AS NVARCHAR(MAX)) LIKE '%критич%';

objtype | objname      | name           | value
--------|--------------|----------------|-------
TABLE   | LogEntries   | MS_Description | Критичная таблица журнала