删除数据中的所有对象

参照于邹建出版的书

/*----------------------------------------------------------------------
--删除数据中的所有对象
--默认值或DEFAULT约束;FOREIGN KEY约束;CHECK约束;PRIMARY KEY 约束;UNIQUE约束
--触发器;表函数;视图;存储过程
--yangys 2006/03/29
--请严重主意:删除默认值对插入表记录使用时的影响
-----------------------------------------------------------------------*/
/*-----------------------------------------------------------
--删除数据库中的所有:
--DEFAULT约束;FOREIGN KEY约束;CHECK约束;PRIMARY KEY 约束;UNIQUE约束
*/
--首先独立删除外健约束
declare @strsql varchar(8000)
declare curTemp cursor local for
select s='alter table ['+object_name(parent_obj)+'] drop constraint ['+name+']'
from sysobjects where xtype ='F'

open curTemp
fetch next from curTemp into @strsql
while @@fetch_status=0
begin
 exec(@strsql)
 fetch next from curTemp into @strsql
end
close curTemp
deallocate curTemp
go

--这里会删除默认值
--DEFAULT约束CHECK约束;PRIMARY KEY 约束;UNIQUE约束
declare @strsql varchar(8000)
declare curTemp cursor local for
select s='alter table ['+object_name(parent_obj)+'] drop constraint ['+name+']'
from sysobjects where xtype in('F','C','D','PK','UQ')

open curTemp
fetch next from curTemp into @strsql
while @@fetch_status=0
begin
 exec(@strsql)
 fetch next from curTemp into @strsql
end
close curTemp
deallocate curTemp

go

/*-------------------------------------------------
--删除数据库中的所有:
--触发器;表函数;视图;存储过程
*/
declare @strsql varchar(8000)
declare curTemp cursor local for
select
 s='drop ' +
 case xtype
  when N'TR' then 'trigger'
  when N'FN' then 'function'
  when N'V' then 'view'
  when N'P' then 'proc'
  else '' end
       +' ['+name+']'
from sysobjects where xtype in('TR','FN','V','P') and [name]<>'syssegments' and [name]<>'sysconstraints'

open curTemp
fetch next from curTemp into @strsql
while @@fetch_status=0
begin
 exec(@strsql)
 fetch next from curTemp into @strsql
end
close curTemp
deallocate curTemp

go