SQLServer表、列备注管理

在开发时，为了方便，在SQL SERVER库内对表名、列名进行备注，但在DB部署客户时，想一次清理上述备注值。

特编写如下 SQL，一次清除上述备注内容：

declare @table nvarchar()

declare @colName nvarchar()

declare @description nvarchar()

declare @descType nvarchar()

declare @name nvarchar()

declare @level0typeV nvarchar()

declare @level0nameV nvarchar()

declare @level1typeV nvarchar()

declare @level1nameV nvarchar()

declare @level2typeV nvarchar()

declare @level2nameV nvarchar()

select  @name= 'MS_Description',

        @level0typeV = 'SCHEMA',

        @level0nameV = 'dbo',

        @level1typeV = 'TABLE',

        @level2typeV = 'COLUMN'

declare cur_desc cursor for

   SELECT

        convert(varchar(), A.name) AS table_name,

        convert(varchar(),B.name) AS column_name,

        ISNULL(convert(varchar(), C.value),'') AS Description,

        'Column' as DescType

    FROM sys.tables A

       Inner JOIN sys.columns B ON B.object_id = A.object_id

       LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id

   union All

   SELECT

        convert(varchar(), A.name) AS table_name,

        null AS column_name,

        ISNULL(convert(varchar(), C.value),'') AS Description,

        'Table' as DescType

    FROM sys.tables A

       LEFT JOIN sys.extended_properties C ON C.major_id = A.object_id

open cur_desc

FETCH NEXT FROM cur_desc INTO @table, @colName, @description, @colName

WHILE @@FETCH_STATUS =

BEGIN

    if @descType = 'Column'

         EXEC sp_dropextendedproperty @name = 'MS_Description', @Level0type = @level0typeV, @Level0name = @level0nameV,

              @Level1type = @level1typeV, @Level1name = @table, @Level2type = @level2typeV, @Level2name = @colName

    else

         EXEC sp_dropextendedproperty @name = 'MS_Description', @Level0type = @level0typeV, @Level0name = @level0nameV,

              @Level1type = @level1typeV, @Level1name = @table

    FETCH NEXT FROM cur_desc INTO @table, @colName, @description, @descType

END

CLOSE cur_desc;

DEALLOCATE cur_desc;