1、删除所有用户表
a:当表之间有外键约束时,会执行失败
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 sp_msforeachtable @command1='drop table ?'
b:先删除约束,再删除表
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 --/第1步**********删除所有表的外键约束*************************/ 2 3 DECLARE c1 cursor for 4 select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; ' 5 from sysobjects 6 where xtype = 'F' 7 open c1 8 declare @c1 varchar(8000) 9 fetch next from c1 into @c1 10 while(@@fetch_status=0) 11 begin 12 exec(@c1) 13 fetch next from c1 into @c1 14 end 15 close c1 16 deallocate c1 17 18 --/第2步**********删除所有表*************************/ 19 20 GO 21 declare @sql varchar(max) 22 while (select count(*) from sysobjects where type='U')>0 23 begin 24 SELECT @sql='drop table ' + name 25 FROM sysobjects 26 WHERE (type = 'U') 27 ORDER BY 'drop table ' + name 28 exec(@sql) 29 end