删除数据库的所有存储过程、主键、外键、索引等

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

--删除存储过程

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航