--存储过程，删除某数据库中所有的用户表，游标的应用，动态SQL的使用
--思路：先删除所有的外键，再删除所有的表；以免外键的存在导致不能删表
--sys.objects表中parent_object_id表示某对象所依附的对象的ID，如外键所在表的ID
--sys.foreign_keys表中有所有外键的信息，也有parent_object_id属性
create database test
go
use test
go
create proc dropAllUserTable as
begin
--声明游标，获得外键的名字及其所在的表的对象ID，
--sys.objects中type in ['F','U']分别表示外键及用户表
declare cursorForeignKey cursor for
select [name], parent_object_id from sys.objects where [type]='F'
open cursorForeignKey
declare @fkName nvarchar(30), @objId int, @tn nvarchar(30)
--提取外键的名字及其所在的表的对象ID到变量@fkName, @objId中
fetch next from cursorForeignKey into @fkName, @objId
while @@fetch_status=0 --删除所有的外键
begin
select @tn=[name] from sys.objects where [object_id]= @objId
set @tn=quotename(@tn)
exec('alter table ' + @tn + ' drop constraint ' + @fkName)
fetch next from cursorForeignKey into @fkName, @objId
end
close cursorForeignKey
deallocate cursorForeignKey
declare cursorTableName cursor for
select [name] from sys.objects where type='U'
open cursorTableName
fetch next from cursorTableName into @tn
while @@fetch_status=0 --删除所有的表
begin
set @tn=quotename(@tn)
exec ('drop table ' + @tn)
fetch next from cursorTableName into @tn
end
close cursorTableName
deallocate cursorTableName
end
go
--测试，a，b两表相互参照
create table a(a int primary key, b int)
create table b(a int primary key, b int references a(a))
alter table a add foreign key(b) references b(a)
--drop table a,b --出错！
exec dropAllUserTable --调用存储过程，删除所有用户表
