Sp addextendedproperty: примеры (SQL)

Примеры использования функции

Добавление свойства к схеме:

EXEC sp_addextendedproperty 
    @name = N'Описание',
    @value = N'Схема для модуля продаж',
    @level0type = N'SCHEMA', @level0name = N'Sales';

Команда выполнена успешно.

Добавление свойства к таблице:

EXEC sp_addextendedproperty 
    @name = N'Версия',
    @value = '1.0',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'Customers';

Команда выполнена успешно.

Добавление свойства к столбцу таблицы:

EXEC sp_addextendedproperty 
    @name = N'Маска ввода',
    @value = N'999-999-9999',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'Customers',
    @level2type = N'COLUMN', @level2name = N'Phone';

Команда выполнена успешно.

Попытка добавления свойства с именем, которое уже существует для объекта, вызывает ошибку.

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

sp_updateextendedproperty: Используется для изменения значения существующего расширенного свойства. Применяется, когда требуется обновить информацию, а не добавить новую.

sp_dropextendedproperty: Предназначена для удаления расширенного свойства с объекта базы данных.

fn_listextendedproperty: Функция для получения списка расширенных свойств, связанных с объектом. Полезна для запросов на чтение метаданных.

Предпочтительнее использовать sp_addextendedproperty для первоначального создания аннотаций. Если свойство уже существует и нужно изменить его значение, логично применить sp_updateextendedproperty. Для полного удаления метаданных подходит sp_dropextendedproperty.

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

MySQL: Аналогичные метаданные часто хранятся в столбце COMMENT для таблиц и столбцов. Для добавления комментария используется инструкция ALTER TABLE.

ALTER TABLE Customers 
    MODIFY COLUMN Phone VARCHAR(20) 
    COMMENT 'Номер телефона в формате 999-999-9999';

Query OK, 0 rows affected.

Oracle: Используется процедура DBMS_METADATA.SET_ATTRIBUTE или комментарии через COMMENT ON.

COMMENT ON COLUMN Customers.Phone IS 'Номер телефона';

Comment created.

PostgreSQL: Поддерживается команда COMMENT, которая привязывает комментарии к объектам базы данных.

COMMENT ON COLUMN customers.phone IS 'Контактный номер';

COMMENT

SQLite: Не имеет встроенной системы расширенных свойств. Дополнительные метаданные обычно хранят в отдельной таблице.

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

Типичные ошибки при использовании

Ошибка из-за неправильного указания иерархии уровней объекта:

EXEC sp_addextendedproperty 
    @name = N'Тест',
    @value = N'Ошибка',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'COLUMN', @level1name = N'Phone';

Msg 15233, Level 16, State 1 ...
Свойства не могут быть добавлены для объекта "dbo". Либо объект не существует, либо нет разрешений.

Попытка добавить свойство с уже существующим именем для того же объекта:

EXEC sp_addextendedproperty 
    @name = N'Версия',
    @value = '2.0',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'Customers';

Msg 15234, Level 16, State 1 ...
Свойство уже существует.

Ошибка при передаче NULL в обязательный параметр @name:

EXEC sp_addextendedproperty 
    @name = NULL,
    @value = N'Значение';

Msg 15149, Level 16, State 1 ...
Параметр @name не может быть NULL.

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

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

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

Добавление нескольких свойств к разным объектам в транзакции для обеспечения целостности:

BEGIN TRANSACTION;
BEGIN TRY
    EXEC sp_addextendedproperty 
        @name = N'Автор', @value = N'Иванов',
        @level0type = N'SCHEMA', @level0name = N'dbo',
        @level1type = N'TABLE', @level1name = N'Orders';

    EXEC sp_addextendedproperty 
        @name = N'Дата создания', @value = '2023-10-01',
        @level0type = N'SCHEMA', @level0name = N'dbo',
        @level1type = N'TABLE', @level1name = N'Orders';
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Команда выполнена успешно.

Использование расширенных свойств для управления версиями схемы базы данных:

EXEC sp_addextendedproperty 
    @name = N'ВерсияСхемы',
    @value = N'2.1.4',
    @level0type = N'SCHEMA', @level0name = N'dbo';

Команда выполнена успешно.

Добавление свойства к индексу:

EXEC sp_addextendedproperty 
    @name = N'Тип индекса',
    @value = N'Оптимизирован для чтения',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE', @level1name = N'Orders',
    @level2type = N'INDEX', @level2name = N'IX_Orders_Date';

Команда выполнена успешно.

Хранение JSON строки в качестве значения свойства для сложной конфигурации:

EXEC sp_addextendedproperty 
    @name = N'НастройкиОтчета',
    @value = N'{"format": "pdf", "language": "ru"}',
    @level0type = N'SCHEMA', @level0name = N'Reporting',
    @level1type = N'PROCEDURE', @level1name = N'GenerateSalesReport';

Команда выполнена успешно.