删除Sqlserver数据库中所有表和字段的备注说明

删除Sqlserver数据库中所有表和字段的备注说明

 

　　有时我们有这样的需求，我们做了一个产品发布到互联网上，但又不想让用户知道了数据库各个表名、字段的备注信息（这些信息我们可能在设计时放了中文名称），别人用个PowerDesigner逆向工程，一下子就把你的设计给导进去了，中文都暴露在用户眼前，有时这不是我们所期望的，那么在SqlServer中如何删除数据库中所有表和字段的备注说明呢，下面的存储过程即可做到！　　

IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   name = 'P_DeleteDscirption'
                    AND type = 'p' ) 
    BEGIN
        DROP PROCEDURE P_DeleteDscirption
    END
GO
CREATE PROCEDURE P_DeleteDscirption
AS 
    BEGIN
    
        IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
            DROP TABLE #temp
            
        DECLARE @index INT ,
            @rowCount INT ,
            @tableID INT ,
            @colID INT ,
            @Sql NVARCHAR(4000) ,
            @colName NVARCHAR(200) ,
            @tableName NVARCHAR(200) 
        -------------缓存临时数据    
        SELECT  t.name AS TableName ,
                c.name AS ColumnName ,
                c.object_id AS TableID ,
                c.column_id AS ColID
        INTO    #temp
        FROM    sys.columns AS c
                LEFT JOIN sys.objects AS t ON c.object_id = t.object_id
        WHERE   t.type = 'u'
        
        -------------获取循环阀值
        SELECT  @index = 0 ,
                @rowCount = COUNT(1)
        FROM    (SELECT DISTINCT
                        TableName
                 FROM   #temp
                ) AS t
        
        WHILE (@index < @rowCount) 
            BEGIN
                SELECT  @Sql = N'' ,
                        @index = @index + 1
                -------------取出表名和表的object_id      
                SELECT  @tableName = t.TableName ,
                        @tableID = T.TableID
                FROM    (SELECT DISTINCT
                                TableName ,TableID,
                                DENSE_RANK() OVER (ORDER BY TableName) AS [rowIndex]
                         FROM   #temp
                        ) AS t
                WHERE   t.rowIndex = @index 
                -------------判断表是否有说明
                IF EXISTS ( SELECT  * FROM    sys.extended_properties WHERE   major_id = @tableID AND minor_id = 0 AND name = N'MS_Description' ) 
                    BEGIN
                        SET @Sql = @Sql
                            + 'EXEC sys.sp_dropextendedproperty @name = N''MS_Description'' ,@level0type = N''SCHEMA'', @level0name = N''dbo'' , @level1type = N''TABLE'' ,@level1name = '
                            + 'N''' + @tableName + '''' + ';' + CHAR(13)
              
                        EXECUTE sys.sp_executesql @Sql
                        PRINT @Sql
                    END
            END
 
        -------------获取循环阀值
        SELECT  @index = 0 ,
                @rowCount = COUNT(1)
        FROM    #temp
 
 
        WHILE (@index < @rowCount) 
            BEGIN
                SELECT  @Sql = N'' ,
                        @index = @index + 1
                
                -------------取出表明列名，object_id,column_id
                SELECT  @tableID = t.TableID ,
                        @colID = t.ColID ,
                        @tableName = t.TableName ,
                        @colName = t.ColumnName
                FROM    (SELECT DISTINCT TableName , ColumnName , colID, TableID,
                                ROW_NUMBER() OVER (ORDER BY TableName, ColumnName) AS [rowIndex]
                         FROM   #temp
                        ) AS t
                WHERE   t.rowIndex = @index
                -------------判断列是否存在说明
                IF EXISTS ( SELECT  * FROM sys.extended_properties WHERE major_id = @tableID AND minor_id = @colID AND name = N'MS_Description' ) 
                    BEGIN
                        SET @Sql = @Sql
                            + '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'' + @colName + ''';'
                            + CHAR(13)
                        
                        PRINT @Sql
                        EXECUTE sys.sp_executesql @Sql
                    END
            END
        -------------清除临时表
        IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
            DROP TABLE #temp
    END