Drop All SQL Server Objects

The following script will drop all of the SQL Server objects.

PLEASE. USE. CAUTION.

DECLARE @n CHAR(1)
SET @n = CHAR(10)

DECLARE @stmt NVARCHAR(MAX)

PRINT 'Dropping Stored Procedures'
-- PROCEDURES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP PROCEDURE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.procedures

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Check Constraints'
-- CHECK CONSTRAINTS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.check_constraints

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Functions'
-- FUNCTIONS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP FUNCTION [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.objects
WHERE   TYPE IN ( 'FN', 'IF', 'TF' )

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Views'
-- VIEWS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP VIEW [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.views

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Foreign Keys'
-- FOREIGN KEYS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.foreign_keys

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Tables'
-- TABLES
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP TABLE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.tables

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Types'
-- USER DEFINED TYPES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP TYPE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.types
WHERE   is_user_defined = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Schemas'
-- SCHEMAS
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP SCHEMA [' + name + ']'
FROM    sys.schemas
WHERE   name <> N'dbo'
  AND   principal_id = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.

Twitter 

Comments are closed.