mssql批量删除表，批量增加列

--批量删除用户表
--1.删除外键约束
DECLARE c1 cursor for 
    select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
    from sysobjects 
    where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    begin 
        exec(@c1)
        fetch next from c1 into @c1
    end
close c1
deallocate c1 
--2.删除表
DECLARE c2 cursor for 
    select 'drop table ['+name +']; '
    from sysobjects 
    where xtype = 'u' 
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
    begin
        exec(@c2)
        fetch next from c2 into @c2
    end
close c2
deallocate c2
--批量清除表内容：
--1.禁用外键约束
DECLARE c1 cursor for 
    select 'alter table ['+ object_name(parent_obj) + '] nocheck constraint ['+name+']; '
    from sysobjects 
    where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    begin 
        exec(@c1)
        fetch next from c1 into @c1
    end
close c1
deallocate c1 
--2.清除表内容
DECLARE c2 cursor for 
    select 'truncate table ['+name +']; '
    from sysobjects 
    where xtype = 'u' 
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
    begin
        exec(@c2)
        fetch next from c2 into @c2
    end
close c2
deallocate c2
--3.启用外键约束
DECLARE c1 cursor for 
    select 'alter table ['+ object_name(parent_obj) + '] check constraint ['+name+']; '
    from sysobjects 
    where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    begin 
        exec(@c1)
        fetch next from c1 into @c1
    end
close c1
deallocate c1

 

批量增加，删除列

-- 批量加列
declare @tablename varchar(50)
declare @sql varchar(1000)
declare cu_adcolumn cursor  for select name from sys.tables 
where  name like 'T_%'

open cu_adcolumn
fetch next from cu_adcolumn into @tablename
while(@@fetch_status=0)
begin

set @sql = 'alter table '+@tablename+' add useraddress varchar(255) null'

execute(@sql)

fetch next from cu_adcolumn into @tablename
end
close cu_adcolumn
deallocate cu_adcolumn

-- 批量删列
DECLARE @table varchar(50) 
DECLARE My_Cursor CURSOR --定义游标
FOR 
( SELECT a.Name 
FROM SysObjects  a  inner  join 
syscolumns  b on a.id = b.id
 Where a.xtype in ('U')  and b.name='USERADDRESS' and b.name like'T_%'
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @table; --读取第一行数据( )
WHILE @@FETCH_STATUS = 0
    BEGIN
   exec( 'alter table ' + @table + ' drop column USERADDRESS' )
   FETCH NEXT FROM My_Cursor into @table; --读取下一行数据
    END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO