Wednesday, March 26, 2008

Drop All Objects in a SQL Server Database

Here is a script that I created that will drop all the objects in a SQL Server database. It was created using a lot of other scripts out on the Internet as inspiration. I like this one does because of the following:

  • Does not use cursors (which a lot of the ones I saw did)
  • Will drop the foreign key and primary key constraints on the tables prior to dropping the tables.

 

/* 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

 

 

18 comments:

Anonymous said...

Perfect, exactly what I was looking for.

Anonymous said...

Just what I needed, thank you!

Anonymous said...

Excelent!!

Adam Anderson said...

I recently had to do the same thing, but I just had to write my own. See my take on it at my URL (click my name).

Benjamin said...

Thank you. Bravo.

Anonymous said...

Script looks fine, though there is a problems. The script does not perform the check appropriately when there are more than one foriegn key constraint. By including a greater than check against the previous constraint should fix it.

Anonymous said...

Thanks a lot. Saved me a great deal of time.

Shumov said...

also you can add a script to drop all indexes!

/* Drop all indexes */
declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'DECLARE @indexName NVARCHAR(128)'
set @command1 = @command1 + ' DECLARE @dropIndexSql NVARCHAR(4000)'
set @command1 = @command1 + ' DECLARE tableIndexes CURSOR FAST_FORWARD FOR'
set @command1 = @command1 + ' SELECT name FROM sys.indexes'
set @command1 = @command1 + ' WHERE object_id = OBJECT_ID(''?'') AND index_id > 0 AND index_id < 255 AND is_primary_key = 0'
set @command1 = @command1 + ' ORDER BY index_id DESC'
set @command1 = @command1 + ' OPEN tableIndexes FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' WHILE @@fetch_status = 0'
set @command1 = @command1 + ' BEGIN'
set @command1 = @command1 + ' SET @dropIndexSql = N''DROP INDEX ?.['' + @indexName + '']'''
set @command1 = @command1 + ' EXEC sp_executesql @dropIndexSql'
set @command1 = @command1 + ' print @dropIndexSql'
set @command1 = @command1 + ' FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' END'
set @command1 = @command1 + ' CLOSE tableIndexes'
set @command1 = @command1 + ' DEALLOCATE tableIndexes'
Print '-----------------------------------------'
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1
GO

Willem said...

Fantastic, exactly what I was looking for too!

Baldur said...

Works perfectly. Thanks for making this really easy!

123 123 said...

Cool article as for me. It would be great to read more concerning that theme. Thanx for posting that material.
Sexy Lady
Busty escorts London

Frank said...

Wonder wonderful script... Works a treat for us!

dryan said...

FYI. Doesn't support schemas.

Anonymous said...

Excellent script!!!

Monica said...

That worked perfectly!!! Thank you SO MUCH!!

Max Nunes said...

Thanks. This help me alot!

Scott Munro said...

Good work Paige. I would be interested to know though why you explicitly dropped the primary key constraints. Won't they get dropped along with the tables?

Paige Cook said...

Scott Munro... Yes you are right the primary key constraints will be dropped along with the tables. However, when I was working with this, I was having some issues initially, so I included the constraints to ensure there was nothing remaining that could be an issue.