删除数据库的所有存储过程、主键、外键、索引等 --删除存储过程 DECLARE@STRINGVARCHAR(8000) WHILEEXISTS(SELECT NAME FROM SYSOBJECTS WHERE TYPE='P'AND STATUS>=0) BEGIN SELECT@STRING='DROP PROCEDURE '+NAME FROM SYSOBJECTS WHERE TYPE ='P'AND STATUS>=0 --SELECT @STRING EXEC(@STRING) END GO --默认值或 DEFAULT 约束 DECLARE@STRINGVARCHAR(8000) WHILEEXISTS(SELECT NAME FROM SYSOBJECTS WHERE XTYPE='D') BEGIN SELECT@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 WHEREOBJECTPROPERTY(ID, N'ISUSERTABLE') =1) B WHERE A.PARENT_OBJ=B.ID EXEC(@STRING) END GO --UNIQUE 约束 DECLARE@STRINGVARCHAR(8000) WHILEEXISTS(SELECT NAME FROM SYSOBJECTS WHERE XTYPE='UQ') BEGIN SELECT@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 WHEREOBJECTPROPERTY(ID, N'ISUSERTABLE') =1) B WHERE A.PARENT_OBJ=B.ID EXEC(@STRING) END GO --FOREIGN KEY 约束 DECLARE@STRINGVARCHAR(8000) WHILEEXISTS(SELECT NAME FROM SYSOBJECTS WHERE TYPE='F') BEGIN SELECT@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 WHEREOBJECTPROPERTY(ID, N'ISUSERTABLE') =1) B WHERE A.PARENT_OBJ=B.ID EXEC(@STRING) END GO --PRIMARY KEY 约束 DECLARE@STRINGVARCHAR(8000) WHILEEXISTS(SELECT NAME FROM SYSOBJECTS WHERE XTYPE='PK') BEGIN SELECT@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 WHEREOBJECTPROPERTY(ID, N'ISUSERTABLE') =1) B WHERE A.PARENT_OBJ=B.ID EXEC(@STRING) END GO --触发器 DECLARE@STRINGVARCHAR(8000) WHILEEXISTS(SELECT NAME FROM SYSOBJECTS WHERE XTYPE='TR') BEGIN SELECT@STRING='DROP TRIGGER '+NAME FROM SYSOBJECTS WHERE XTYPE='TR' EXEC(@STRING) END GO --索引 declare@stringvarchar(8000) whileexists( select TABLE_NAME= o.name,INDEX_NAME= x.name from sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk where o.type in ('U') andconvert(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 andpermissions(o.id, c.name) <>0 and (x.status&32) =0-- No hypothetical indexes groupby o.name,x.name) begin selecttop1@string='drop index '+o.name+'.'+ x.name from sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk where o.type in ('U') andconvert(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 andpermissions(o.id, c.name) <>0 and (x.status&32) =0-- No hypothetical indexes groupby o.name,x.name exec(@string) end GO