删除所有的用户表，存储过程，游标的应用，动态SQL的使用

--存储过程，删除某数据库中所有的用户表，游标的应用，动态SQL的使用--思路：先删除所有的外键，再删除所有的表；以免外键的存在导致不能删表--sys.objects表中parent_object_id表示某对象所依附的对象的ID，如外键所在表的ID--sys.foreign_keys表中有所有外键的信息，也有parent_object_id属性create database testgouse testgocreate proc dropAllUserTable asbegin--声明游标，获得外键的名字及其所在的表的对象ID，--sys.objects中type in ['F','U']分别表示外键及用户表declare cursorForeignKey cursor for select [name], parent_object_id from sys.objects where [type]='F'open cursorForeignKeydeclare @fkName nvarchar(30), @objId int, @tn nvarchar(30)--提取外键的名字及其所在的表的对象ID到变量@fkName, @objId中fetch next from cursorForeignKey into @fkName, @objIdwhile @@fetch_status=0 --删除所有的外键beginselect @tn=[name] from sys.objects where [object_id]= @objIdset @tn=quotename(@tn)exec('alter table ' + @tn + ' drop constraint ' + @fkName)fetch next from cursorForeignKey into @fkName, @objIdendclose cursorForeignKeydeallocate cursorForeignKeydeclare cursorTableName cursor for select [name] from sys.objects where type='U'open cursorTableNamefetch next from cursorTableName into @tnwhile @@fetch_status=0 --删除所有的表beginset @tn=quotename(@tn)exec ('drop table ' + @tn)fetch next from cursorTableName into @tnendclose cursorTableNamedeallocate cursorTableNameendgo--测试，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 --调用存储过程，删除所有用户表