SQLServer表、列备注管理

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

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

 
declare @table nvarchar(500)
declare @colName nvarchar(500)
declare @description nvarchar(500)
declare @descType nvarchar(500)
declare @name nvarchar(500)
declare @level0typeV nvarchar(500)
declare @level0nameV nvarchar(500)
declare @level1typeV nvarchar(500)
declare @level1nameV nvarchar(500)
declare @level2typeV nvarchar(500)
declare @level2nameV nvarchar(500)

select  @name= 'MS_Description',  
        @level0typeV = 'SCHEMA',
        @level0nameV = 'dbo',
        @level1typeV = 'TABLE',
        @level2typeV = 'COLUMN'
 
 
declare cur_desc cursor for
   SELECT
        convert(varchar(500), A.name) AS table_name,
        convert(varchar(500),B.name) AS column_name,
        ISNULL(convert(varchar(500), 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(500), A.name) AS table_name,
        null AS column_name,
        ISNULL(convert(varchar(500), 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 = 0
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;
 
 

 

转载于:https://www.cnblogs.com/todayhero/p/10418349.html