/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'P\' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = \'DROP PROCEDURE [dbo].[\' + RTRIM(@name) +\']\'
EXEC (@SQL)
PRINT \'Dropped Procedure: \' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'P\' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'V\' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = \'DROP VIEW [dbo].[\' + RTRIM(@name) +\']\'
EXEC (@SQL)
PRINT \'Dropped View: \' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'V\' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] IN (N\'FN\', N\'IF\', N\'TF\', N\'FS\', N\'FT\') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = \'DROP FUNCTION [dbo].[\' + RTRIM(@name) +\']\'
EXEC (@SQL)
PRINT \'Dropped Function: \' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] IN (N\'FN\', N\'IF\', N\'TF\', N\'FS\', N\'FT\') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @CONSTRAINT VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @CONSTRAINT IS NOT NULL
BEGIN
SELECT @SQL = \'ALTER TABLE [dbo].[\' + RTRIM(@name) +\'] DROP CONSTRAINT [\' + RTRIM(@CONSTRAINT) +\']\'
EXEC (@SQL)
PRINT \'Dropped FK Constraint: \' + @CONSTRAINT + \' on \' + @name
SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' AND CONSTRAINT_NAME <> @CONSTRAINT AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @CONSTRAINT VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @CONSTRAINT IS NOT NULL
BEGIN
SELECT @SQL = \'ALTER TABLE [dbo].[\' + RTRIM(@name) +\'] DROP CONSTRAINT [\' + RTRIM(@CONSTRAINT)+\']\'
EXEC (@SQL)
PRINT \'Dropped PK Constraint: \' + @CONSTRAINT + \' on \' + @name
SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' AND CONSTRAINT_NAME <> @CONSTRAINT AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'U\' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = \'DROP TABLE [dbo].[\' + RTRIM(@name) +\']\'
EXEC (@SQL)
PRINT \'Dropped Table: \' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'U\' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO