批量清除SQL SERVER数据库中字段的描述

DECLARE @ID nvarchar(50)
DECLARE @Name nvarchar(50)
DECLARE @ID2 nvarchar(50)
DECLARE @Name2 nvarchar(50)
DECLARE @ID3 nvarchar(50)

DECLARE my_cursor CURSOR FOR select name,object_id from sys.tables where type='U'
Open my_cursor
fetch my_cursor into @Name,@ID
while @@fetch_status=0
begin
 --SELECT @Name,@ID
    
    DECLARE my_cursor2 CURSOR FOR  select name,object_id,column_id from sys.columns where object_id=@ID
    Open my_cursor2
 fetch my_cursor2 into @Name2,@ID2,@ID3
 while @@fetch_status=0
 begin
  --select @ID2,@Name2
  declare @cnt int
  select @cnt=COUNT(*) from sys.extended_properties where major_id=@ID and minor_id=@ID3 and
  name='MS_Description'
  if(@cnt>0)
  begin
  EXEC sp_dropextendedproperty  
  @name = 'MS_Description'  
     ,@level0type = 'schema'  
  ,@level0name = dbo 
  ,@level1type = 'table' 
  ,@level1name = @Name 
  ,@level2type = 'column' 
  ,@level2name =@Name2; 
  print '表['+@Name+']字段['+@Name2+']说明清除成功!'
  end
  fetch my_cursor2 into @Name2,@ID2,@ID3
 end
 close my_cursor2
 deallocate my_cursor2
 fetch my_cursor into @Name,@ID
end
close my_cursor
deallocate my_cursor

  

posted on 2014-08-15 17:22  记性特差  阅读(231)  评论(0)    收藏  举报