CAUTION: This is a very powerful script, use at your own risk!
This script will:
- Drop all non-system stored procs
- Drop all views
- Drop all functions
- Drop all Foreign Key constraints
- Drop all Primary Key constraints
- Drop all tables
USE < Database Name > /* 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 |
Last Updated on October 26, 2015