Drop all objects in a database on SQL Server

Solution for Drop all objects from database at once!

Following procedure run on any database (SQL Server)

\***********************************************/


CREATE PROCEDURE dbo.DropAllDBObjects
@Action VARCHAR(20)=NULL
As
BEGIN

DECLARE @Name VARCHAR(500)

IF @Action='Table'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.TABLES

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP TABLE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END

ELSE IF @Action='PROCEDURE'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
IF @Name <> 'DropAllDBObjects'
EXEC('DROP PROCEDURE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END
ELSE IF @Action='Function'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP FUNCTION ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END
ELSE IF @Action='View'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP VIEW ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END
IF @Action is null
BEGIN
--Table
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.TABLES

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP TABLE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

--Views
DECLARE DROPALL CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP VIEW ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

--Functions
DECLARE DROPALL CURSOR
FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP FUNCTION ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

--Procedure
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
IF @Name <> 'DropAllDBObjects'
EXEC('DROP PROCEDURE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL
END
END
GO

\***********************************************/

To execute Procedure:

a)To Drop all objects from Database,execute following

EXEC DropAllDBObjects

b)To Drop all objects from Database,based on different object at time.

EXEC DropAllDBObjects 'table'

or

EXEC DropAllDBObjects 'View'

or

EXEC DropAllDBObjects 'Function'

or

EXEC DropAllDBObjects 'Procedure'


To View all objects,use following

SELECT [NAME] FROM sys.TABLES
SELECT [NAME] FROM sys.objects WHERE TYPE='P'
SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

After All work done,you can drop following procedure

DROP PROC DropAllDBObjects