Deletes all stored procedures, primary keys, foreign keys, and indexes of a database.

-- Delete a stored procedure
DECLARE @ string varchar (8000)
While exists (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 value or DEFAULT Constraint
DECLARE @ string varchar (8000)
While exists (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'),
(Select id, name from sysobjects where objectproperty (ID, N 'isusertable') = 1) B
Where a. PARENT_OBJ = B. ID
EXEC (@ STRING)
END

GO

-- UNIQUE constraints
DECLARE @ string varchar (8000)
While exists (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'),
(Select id, name from sysobjects where objectproperty (ID, N 'isusertable') = 1) B
Where a. PARENT_OBJ = B. ID
EXEC (@ STRING)
END

GO

-- Foreign key constraint
DECLARE @ string varchar (8000)
While exists (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'),
(Select id, name from sysobjects where objectproperty (ID, N 'isusertable') = 1) B
Where a. PARENT_OBJ = B. ID
EXEC (@ STRING)
END

GO

-- Primary key constraint
DECLARE @ string varchar (8000)
While exists (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'),
(Select id, name from sysobjects where objectproperty (ID, N 'isusertable') = 1) B
Where a. PARENT_OBJ = B. ID
EXEC (@ STRING)
END

GO

-- Trigger
DECLARE @ string varchar (8000)
While exists (select name from sysobjects where xtype = 'tr ')
BEGIN
SELECT @ STRING = 'drop trigger' + name from sysobjects where xtype = 'tr'
EXEC (@ STRING)
END

GO

-- Index
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