小贝子编程

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



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

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

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';

下面是另一种存储过程方法，类似于Ruslan K。

-- simplify syntax for maintaining data dictionary
IF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_addorupdatedescription;
GO
CREATE PROCEDURE usp_addorupdatedescription
        @table nvarchar(128),  -- table name
        @column nvarchar(128), -- column name, NULL if description for table
        @descr sql_variant     -- description text
AS
    BEGIN
        SET NOCOUNT ON;
        IF @column IS NOT NULL
            IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
            WHERE [major_id] = OBJECT_ID(@table) AND [name] = N'MS_Description'
                  AND [minor_id] = (SELECT [column_id]
                                    FROM SYS.COLUMNS WHERE [name] = @column AND [object_id] = OBJECT_ID(@table)))
                EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = @descr,
                                               @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
                                               @level1name = @table, @level2type = N'COLUMN', @level2name = @column;
            ELSE
                EXECUTE sp_updateextendedproperty @name = N'MS_Description',
                                                  @value = @descr, @level0type = N'SCHEMA', @level0name = N'dbo',
                                                  @level1type = N'TABLE', @level1name = @table,
                                                  @level2type = N'COLUMN', @level2name = @column;
        ELSE
            IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
            WHERE [major_id] = OBJECT_ID(@table) AND [name] = N'MS_Description'
                  AND [minor_id] = 0)
                EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = @descr,
                                               @level0type = N'SCHEMA', @level0name = N'dbo',
                                               @level1type = N'TABLE', @level1name = @table;
            ELSE
                EXECUTE sp_updateextendedproperty @name = N'MS_Description', @value = @descr,
                                                  @level0type = N'SCHEMA', @level0name = N'dbo',
                                                  @level1type = N'TABLE', @level1name = @table;
    END
GO

我编写了一个简单的存储过程来添加或更新扩展属性'MS_Description':

IF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_addorupdatedescription;
GO
CREATE PROCEDURE usp_addorupdatedescription
    @table nvarchar(128),  -- table name
    @column nvarchar(128), -- column name, NULL if description for table
    @descr sql_variant     -- description text
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @c nvarchar(128) = NULL;
    IF @column IS NOT NULL
        SET @c = N'COLUMN';
    BEGIN TRY
        EXECUTE sp_updateextendedproperty  N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
    END TRY
    BEGIN CATCH
        EXECUTE sp_addextendedproperty N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
    END CATCH;
END
GO

也许我的回答没有直接连接到这个问题，但我想指出，MS_Description实际上是区分大小写的，即使我们添加它与SQL。如果我们使用MS_DESCRIPTION而不是MS_DESCRIPTION，它将不会显示在SMSS表设计视图中。

在我的例子中，我必须做这样的事情来删除现有的描述并添加一个正确的描述。

IF EXISTS (
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('TableName')
        AND [name] = N'MS_DESCRIPTION'
        AND [minor_id] = (
            SELECT [column_id]
            FROM SYS.COLUMNS
            WHERE [name] = 'ColumnName'
                AND [object_id] = OBJECT_ID('Tablename')
            )
    )
    EXEC sys.sp_dropextendedproperty @name = N'MS_DESCRIPTION'
        ,@level0type = N'SCHEMA'
        ,@level0name = N'dbo'
        ,@level1type = N'TABLE'
        ,@level1name = N'TableName'
        ,@level2type = N'COLUMN'
        ,@level2name = N'ColumnName'
    GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = N'Description detail'
,@level0type = N'SCHEMA'
,@level0name = N'dbo'
,@level1type = N'TABLE'
,@level1name = N'TableName'
,@level2type = N'COLUMN'
,@level2name = N'ColumnName'
去

请找到我的扩展到Brian Westrich的答案上面，但这个版本允许更新和添加表和列上的任何扩展属性，而不仅仅是MS_Description。此外，它还允许您使用存储过程在不同的数据库中添加和更新扩展属性，因此在服务器上只需要一个副本。

CREATE PROCEDURE dbo.AddOrUpdateExtendedProperty
     @Database      NVARCHAR(128)   -- Database name
    ,@Schema        NVARCHAR(128)   -- Schema name
    ,@Table         NVARCHAR(128)   -- Table name
    ,@Column        NVARCHAR(128)   -- Column name, NULL if description for table
    ,@PropertyName  NVARCHAR(128)   -- Property name
    ,@PropertyValue SQL_VARIANT     -- Property value
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @NSQL NVARCHAR(MAX);
    DECLARE @Level2Type NVARCHAR(128) = NULL;
    DECLARE @Params NVARCHAR(MAX) = N'@Schema NVARCHAR(128), @Table NVARCHAR(128), @Column NVARCHAR(128), @PropertyName NVARCHAR(128), @PropertyValue SQL_VARIANT';
    IF @Column IS NOT NULL
    BEGIN
        SET @NSQL = 'USE ' + @Database + ';
            IF NOT EXISTS
            (
                SELECT NULL FROM sys.extended_properties
                 WHERE     major_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
                       AND name = @PropertyName
                      AND minor_id = (SELECT column_id
                                        FROM sys.columns
                                       WHERE     name = @Column
                                             AND object_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
                                     )
            )
            BEGIN
                EXECUTE sp_addextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table
                    ,@level2type = N''COLUMN''
                    ,@level2name = @Column;
            END
            ELSE
            BEGIN
                EXECUTE sp_updateextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table
                    ,@level2type = N''COLUMN''
                    ,@level2name = @Column;
            END
        ';
        EXECUTE sp_executesql
             @NSQL
            ,@Params
            ,@Schema
            ,@Table
            ,@Column
            ,@PropertyName
            ,@PropertyValue;
    END
    ELSE
    BEGIN
        SET @NSQL = 'USE ' + @Database + ';
            IF NOT EXISTS
            (
                SELECT NULL
                  FROM sys.extended_properties
                 WHERE     major_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
                       AND name = @PropertyName
                       AND minor_id = 0
            )
            BEGIN
                EXECUTE sp_addextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table;
            END
            ELSE
            BEGIN
                EXECUTE sp_updateextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table;
            END
        ';
        EXECUTE sp_executesql
             @NSQL
            ,@Params
            ,@Schema
            ,@Table
            ,@Column
            ,@PropertyName
            ,@PropertyValue;
    END
END

检查给定表可用的任何扩展属性，如下所示:

IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID('<schema>.<table_name>') AND [name] = N'MS_Description')

如果您的表有多个扩展属性，则列ID为minor_id。

IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID('<schema>.<table_name>') AND [name] = N'MS_Description' AND minor_id = 3)

查询sys.extended_properties目录视图以获得数据库中的所有扩展属性。

详细信息请访问http://msdn.microsoft.com/en-us/library/ms177541(v=sql.110).aspx

基于ScubaSteve的回答，以下查询将允许您根据名称检查指定模式中的列或表上的MS_Description属性。要检查表，只需将谓词c.name = '<column>'替换为c.name IS NULL或d.minor_id = 0

按模式枚举MS_Description属性使用-

SELECT a.name as [schema], b.name as [table], c.name as [column], d.name, d.value
FROM sys.schemas a
JOIN sys.tables b ON a.schema_id = b.schema_id
LEFT JOIN sys.columns c ON b.object_id = c.object_id
JOIN sys.extended_properties d ON d.major_id = b.object_id AND d.minor_id = ISNULL(c.column_id,0)
WHERE a.name = '<schema>' AND b.name = '<table>' AND c.name = '<column>' AND d.name = 'MS_Description'

在添加MS_Description属性之前检查它是否不存在-

IF NOT EXISTS (SELECT 1 FROM sys.schemas a JOIN sys.tables b ON a.schema_id = b.schema_id LEFT JOIN sys.columns c ON b.object_id = c.object_id JOIN sys.extended_properties d ON d.major_id = b.object_id AND d.minor_id = ISNULL(c.column_id,0)
               WHERE a.name = '<schema>' AND b.name = '<table>' AND c.name = '<column>' AND d.name = 'MS_Description'
               )
BEGIN
    --EXEC sp_addextendedproperty statement goes here
END

这些查询中的连接可能可以更好地排序以消除ISNULL函数，但这应该得到您正在寻找的

喜欢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

为此，我使用fn_listextendedproperty函数，该函数允许指定模式

declare @description nvarchar(max)=N'New description'
if exists (select 1 from fn_listextendedproperty(N'MS_Description', 
           N'SCHEMA', N'dbo', 
           N'TABLE',N'TableName', N'COLUMN',N'ColumnName'))
   EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@description, 
           @level0type=N'SCHEMA', @level0name=N'dbo',
           @level1type=N'TABLE' , @level1name=N'TableName', 
           @level2type=N'COLUMN', @level2name=N'ColumnName'
ELSE
   EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@description , 
           @level0type=N'SCHEMA', @level0name=N'dbo', 
           @level1type=N'TABLE' ,  @level1name=N'TableName', 
           @level2type=N'COLUMN', @level2name=N'ColumnName'
GO

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium