Script to delete Functions, Stored Procedures, Views & Tables in a SQL Server Database

Delete All Objects in SQL Server.sql

-- Delete All Functions
------------------------------------------------------------------------------------------------------
DECLARE @FUNCNAME VARCHAR(2500)
DECLARE FUNCCURSOR CURSOR FOR SELECT [NAME] FROM SYS.OBJECTS WHERE TYPE = 'FN'
OPEN FUNCCURSOR
FETCH NEXT FROM FUNCCURSOR INTO @FUNCNAME
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC('DROP FUNCTION ' + @FUNCNAME)
		FETCH NEXT FROM FUNCCURSOR INTO @FUNCNAME
	END
CLOSE FUNCCURSOR
DEALLOCATE FUNCCURSOR
------------------------------------------------------------------------------------------------------

-- Delete All Stored Procedures
------------------------------------------------------------------------------------------------------
DECLARE @PROCNAME VARCHAR(2500)
DECLARE SPCURSOR CURSOR FOR SELECT [NAME] FROM SYS.OBJECTS WHERE TYPE = 'P'
OPEN SPCURSOR
FETCH NEXT FROM SPCURSOR INTO @PROCNAME
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC('DROP PROCEDURE ' + @PROCNAME)
		FETCH NEXT FROM SPCURSOR INTO @PROCNAME
	END
CLOSE SPCURSOR
DEALLOCATE SPCURSOR
------------------------------------------------------------------------------------------------------

-- Delete All Views
------------------------------------------------------------------------------------------------------
DECLARE @VIEWNAME VARCHAR(2500)
DECLARE VIEWCURSOR CURSOR FOR SELECT [NAME] FROM SYS.OBJECTS WHERE TYPE = 'V'
OPEN VIEWCURSOR
FETCH NEXT FROM VIEWCURSOR INTO @VIEWNAME
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC('DROP VIEW ' + @VIEWNAME)
		FETCH NEXT FROM VIEWCURSOR INTO @VIEWNAME
	END
CLOSE VIEWCURSOR
DEALLOCATE VIEWCURSOR
------------------------------------------------------------------------------------------------------

-- Delete All Tables
------------------------------------------------------------------------------------------------------
DECLARE @SQL NVARCHAR(500) DECLARE @TABLECURSOR CURSOR
SET @TABLECURSOR = CURSOR FAST_FORWARD FOR
SELECT DISTINCT SQL = 'ALTER TABLE [' + TC2.TABLE_NAME + '] DROP [' + RC1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON TC2.CONSTRAINT_NAME =RC1.CONSTRAINT_NAME
OPEN @TABLECURSOR FETCH NEXT FROM @TABLECURSOR INTO @SQL
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		EXEC SP_EXECUTESQL @SQL
		FETCH NEXT FROM @TABLECURSOR INTO @SQL
	END
CLOSE @TABLECURSOR DEALLOCATE @TABLECURSOR
GO
EXEC SP_MSFOREACHTABLE 'DROP TABLE ?'
GO
------------------------------------------------------------------------------------------------------