Sql Server 删除所有表（转）

http://www.cnblogs.com/jys509/p/3589468.html

 首先必须要清空所有表的外键

 

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

 

 第二部删除所有的表

--清空数据库内所有的表 --注意替换数据库
use aqfk_2016_test
GO
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='U')>0
begin
SELECT @sql='drop table ' + name
FROM sysobjects
WHERE (type = 'U')
ORDER BY 'drop table ' + name
exec(@sql)
end

批量删除所有视图：

 参考网址:http://www.uol123.com/2012/03/02/sql-server%E6%89%B9%E9%87%8F%E5%88%A0%E9%99%A4%E8%A7%86%E5%9B%BE%E3%80%81%E8%A1%A8%E3%80%81%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E3%80%81%E5%87%BD%E6%95%B0%E7%AD%89.html

select identity(int,1,1) flag,[name] names into #tmp
from sysobjects where xtype='v'

declare @tb varchar(1000) ,@a int,@b int,@sql varchar(8000)
select @a=min(flag),@b=max(flag) from #tmp
while @a<=@b
begin
select @tb=names from #tmp where flag=@a
set @sql='drop view "'+@tb+'"'
exec(@sql)
set @a=@a+1
end
DROP TABLE #tmp