在添加之前检查扩展属性描述是否已经存在

Question

所以，我有一个添加扩展属性的脚本，一些描述一个表，一些描述一个列。如何在添加扩展属性之前检查它是否存在，以便脚本不会引发错误？

原文由 ScubaSteve 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

第一个脚本检查描述表的扩展属性是否存在：

 IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('Table_Name') AND [name] = N'MS_Description' AND [minor_id] = 0)
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'This table is responsible for holding information.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Table_Name';

第二个脚本检查描述列的扩展属性是否存在：

 IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('Table_Name') AND [name] = N'MS_Description' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = 'Column_Name' AND [object_id] = OBJECT_ID('Table_Name')))
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'This column is responsible for holding information for table Table_Name.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Table_Name', @level2type = N'COLUMN', @level2name = N'Column_Name';

原文由 ScubaSteve 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

喜欢 Brian Westrich 和 corky_bantam 的答案

以下是我试图实现的两者的组合。不确定是否有人会感兴趣，但我认为我分享一下以防万一。

我希望能够为描述、源字段、源系统等设置多个不同的扩展属性。实际上，我在顶部放置了另一个存储过程，然后我可以将标准值提供给它。

我确实喜欢 corky_bantam 所做的跨数据库运行的选项，但我决定在这个版本中保持简单。也许将来我会发现需要跨数据库运行它，我会偷那个版本:)

我试图简化 if not exist 部分——如果它真的更好的话还有待商榷，但我发现它更容易理解。

我制作了大多数值变量（我的高中 IT 老师会很自豪），所以我可以运行它以获得视图等。

还要注意 @column_name VARCHAR(500) = NULL 我这样做了，所以我在运行时根本不需要提供变量。在对象级别设置值时，我懒得一直放@column_name = NULL

     CREATE OR ALTER PROCEDURE [tools].[sp_set_extended_properties]

        @extended_property_name VARCHAR(500), --the extended property to set/update
        @schema_name VARCHAR(500), --schema name
        @object_name VARCHAR(500), --object name
        @column_name VARCHAR(500) = NULL, -- column name, NULL if description for object
        @value sql_variant --the value to assign
AS
    BEGIN
        SET NOCOUNT ON;


        DECLARE @object_id INT
        DECLARE @object_type VARCHAR(50)
        DECLARE @column_id INT


        SELECT
        @object_id = o.object_id
        ,@object_type = CASE o.type
                            WHEN 'U' THEN 'TABLE'
                            ELSE o.type_desc END
        ,@column_id = c.column_id
        FROM sys.objects o
        INNER JOIN sys.schemas s on o.schema_id = s.schema_id
        INNER JOIN sys.columns c on o.object_id = c.object_id
        WHERE o.name = @object_name
        AND s.name = @schema_name
        AND c.name = ISNULL(@column_name, c.name)

        --SELECT @object_id, @object_type, @column_id

        IF @column_name IS NOT NULL
            IF NOT EXISTS (SELECT NULL
                            FROM SYS.EXTENDED_PROPERTIES
                            WHERE [major_id] = @object_id
                            AND [name] = @extended_property_name
                            AND [minor_id] = @column_id)
                EXECUTE sp_addextendedproperty @name = @extended_property_name, @value = @value,
                                               @level0type = N'SCHEMA', @level0name = @schema_name, @level1type = @object_type,
                                               @level1name = @object_name, @level2type = N'COLUMN', @level2name = @column_name;
            ELSE
                EXECUTE sp_updateextendedproperty @name = @extended_property_name,
                                                  @value = @value, @level0type = N'SCHEMA', @level0name = @schema_name,
                                                  @level1type = @object_type, @level1name = @object_name,
                                                  @level2type = N'COLUMN', @level2name = @column_name;
        ELSE
            IF NOT EXISTS (SELECT NULL
                            FROM SYS.EXTENDED_PROPERTIES
                            WHERE [major_id] = @object_id
                            AND [name] = @extended_property_name
                            AND [minor_id] = 0)
                EXECUTE sp_addextendedproperty @name = @extended_property_name, @value = @value,
                                               @level0type = N'SCHEMA', @level0name = @schema_name,
                                               @level1type = @object_type, @level1name = @object_name;
            ELSE
                EXECUTE sp_updateextendedproperty @name = @extended_property_name, @value = @value,
                                                  @level0type = N'SCHEMA', @level0name = @schema_name,
                                                  @level1type = @object_type, @level1name = @object_name;

    END
GO

原文由 Ashley McLellan 发布，翻译遵循 CC BY-SA 4.0 许可协议