删除SqlServer数据表和列的描述

最近项目有部署到私有云的计划。为了最后的尊严，不能把完整的数据库给对方反向工程了。需要把数据表和列的描述给删掉。

直接上代码，用的游标处理方式。

--表描述
DECLARE @tn NVARCHAR(50)
DECLARE c CURSOR FOR SELECT t.name FROM sys.tables AS t INNER JOIN sys.extended_properties AS p ON t.[object_id]=p.major_id WHERE t.[type]='U' AND p.minor_id = 0 AND p.[name] = N'MS_Description' ORDER BY t.name
OPEN c
FETCH NEXT FROM c INTO @tn
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description' ,@level0type = N'SCHEMA', @level0name = N'gxjt' , @level1type = N'TABLE' ,@level1name = @tn;
    FETCH NEXT FROM c INTO @tn
END
CLOSE c
DEALLOCATE c
GO

--列描述
DECLARE @tn NVARCHAR(50),@cn NVARCHAR(50)
DECLARE c CURSOR FOR SELECT t.name AS tn,l.name AS cn FROM sys.[columns] AS l INNER JOIN sys.extended_properties AS p ON l.[object_id]=p.major_id AND l.column_id=p.minor_id INNER JOIN sys.tables AS t ON l.[object_id]=t.[object_id] WHERE t.[type]='U' AND p.[name] = N'MS_Description' ORDER BY t.name,l.name
OPEN c
FETCH NEXT FROM c INTO @tn,@cn
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description' ,@level0type = N'SCHEMA', @level0name = N'gxjt' , @level1type = N'TABLE' ,@level1name = @tn,@level2type = N'COLUMN',@level2name=@cn;
    FETCH NEXT FROM c INTO @tn,@cn
END
CLOSE c
DEALLOCATE c
GO