sql server 批量删除表

批量删除表
DECLARE @Table NVARCHAR(30)  
DECLARE tmpCur CURSOR FOR   
SELECT name FROM sys.objects WHERE TYPE='U' AND name LIKE N'HSUPA%' 
OPEN tmpCur   
FETCH NEXT FROM tmpCur INTO @Table 
 
WHILE @@FETCH_STATUS = 0   
BEGIN 
    DECLARE @sql VARCHAR(100)  
    SELECT @sql = 'drop table ' + @Table 
    EXEC(@sql)  
    FETCH NEXT FROM tmpCur INTO @Table 
END 
CLOSE tmpCur   
DEALLOCATE tmpCur  
******************************************************************************************************************************
-删除外键约束
DECLARE c1 cursor for
    select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
    from sysobjects
    where xtype = 'F' and object_name(parent_obj) like 'temptb_%'
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

转载于:https://blog.51cto.com/zyfsport/691909