--刪除存儲過程
declare @string varchar(8000)
while exists(select name from sysobjects where type='p' and status>=0)
begin
select top 1 @string='drop procedure '+name from sysobjects where type = 'p' and status>=0
--select @string
exec(@string)
end
go
--默認值或 default 約束
declare @string varchar(8000)
while exists(select name from sysobjects where xtype='d')
begin
select top 1 @string='alter table '+b.name+' drop constraint '+a.name
from (select parent_obj,name from sysobjects where xtype='d') a,
(select id,name from sysobjects where objectproperty(id, n'isusertable') = 1) b
where a.parent_obj=b.id
exec(@string)
end
go
--unique 約束
declare @string varchar(8000)
while exists(select name from sysobjects where xtype='uq')
begin
select top 1 @string='alter table '+b.name+' drop constraint '+a.name
from (select parent_obj,name from sysobjects where xtype='uq') a,
(select id,name from sysobjects where objectproperty(id, n'isusertable') = 1) b
where a.parent_obj=b.id
exec(@string)
end
go
--foreign key 約束
declare @string varchar(8000)
while exists(select name from sysobjects where type='f')
begin
select top 1 @string='alter table '+b.name+' drop constraint '+a.name
from (select parent_obj,name from sysobjects where type='f') a,
(select id,name from sysobjects where objectproperty(id, n'isusertable') = 1) b
where a.parent_obj=b.id
exec(@string)
end
go
--primary key 約束
declare @string varchar(8000)
while exists(select name from sysobjects where xtype='pk')
begin
select top 1 @string='alter table '+b.name+' drop constraint '+a.name
from (select parent_obj,name from sysobjects where xtype='pk') a,
(select id,name from sysobjects where objectproperty(id, n'isusertable') = 1) b
where a.parent_obj=b.id
exec(@string)
end
go
--觸發器
declare @string varchar(8000)
while exists(select name from sysobjects where xtype='tr')
begin
select top 1 @string='drop trigger '+name from sysobjects where xtype='tr'
exec(@string)
end
go
--索引
declare @string varchar(8000)
while exists(
select table_name= o.name,index_name= x.name
from sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
where o.type in ('u')
and convert(bit,(x.status & 0x800)/0x800)=0
and x.id = o.id
and o.id = c.id
and o.id = xk.id
and x.indid = xk.indid
and c.colid = xk.colid
and xk.keyno <= x.keycnt
and permissions(o.id, c.name) <> 0
and (x.status&32) = 0 -- no hypothetical indexes
group by o.name,x.name)
begin
select top 1 @string='drop index '+o.name+'.'+ x.name
from sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
where o.type in ('u')
and convert(bit,(x.status & 0x800)/0x800)=0
and x.id = o.id
and o.id = c.id
and o.id = xk.id
and x.indid = xk.indid
and c.colid = xk.colid
and xk.keyno <= x.keycnt
and permissions(o.id, c.name) <> 0
and (x.status&32) = 0 -- no hypothetical indexes
group by o.name,x.name
exec(@string)
end
go
