--存儲(chǔ)過(guò)程，刪除某數(shù)據(jù)庫(kù)中所有的用戶表，游標(biāo)的應(yīng)用，動(dòng)態(tài)SQL的使用
--思路：先刪除所有的外鍵，再刪除所有的表；以免外鍵的存在導(dǎo)致不能刪表
--sys.objects表中parent_object_id表示某對(duì)象所依附的對(duì)象的ID，如外鍵所在表的ID
--sys.foreign_keys表中有所有外鍵的信息，也有parent_object_id屬性
create database test
go
use test

go
create proc dropAllUserTable as
begin
--聲明游標(biāo)，獲得外鍵的名字及其所在的表的對(duì)象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)
--提取外鍵的名字及其所在的表的對(duì)象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
--測(cè)試，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 --出錯(cuò)！

exec dropAllUserTable --調(diào)用存儲(chǔ)過(guò)程，刪除所有用戶表