/*

Use this sql to drop all objects in a database.

*/

-- Drop all SP

declare @dropSp varchar(max)=''

select @dropSp=@dropSp+'DROP PROCEDURE '+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)

FROM sysobjects s

left join sys.objects obj

on s.id = obj.object_id and s.name=obj.name

WHERE s.[type] = 'P' AND category = 0 ORDER BY s.[name]

--EXEC (@SQL)

Print @dropSp

go

/* Drop all views */

declare @dropViews varchar(max) =''

select @dropViews=@dropViews+'DROP VIEW '+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)

FROM sysobjects s

left join sys.objects obj

on s.id = obj.object_id and s.name=obj.name

WHERE s.[type] = 'V' AND category = 0 ORDER BY s.[name]

--EXEC (@dropViews)

Print @dropViews

go

/* Drop all functions */

declare @dropFN varchar(max) =''

select @dropFN=@dropFN+'DROP FUNCTION '+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)

FROM sysobjects s

left join sys.objects obj

on s.id = obj.object_id and s.name=obj.name

WHERE s.[type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY s.[name]

--EXEC (@dropFN)

Print @dropFN

go

/* Drop all Foreign Key constraints */

DECLARE @SQL VARCHAR(MAX)=''

SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(DB_NAME())+'.'+ QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C

WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

--EXEC (@SQL)

PRINT @SQL

GO

--Drop all primary key

DECLARE @SQL VARCHAR(MAX)=''

SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(DB_NAME())+'.'+ QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C

WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'

--EXEC (@SQL)

PRINT @SQL

GO

/* Drop all tables */

DECLARE @SQL VARCHAR(MAX)=''

select @SQL = @SQL+'drop TABLE '+QUOTENAME(DB_NAME())+'.'+QUOTENAME(SCHEMA_NAME(obj.schema_id))+'.'+ QUOTENAME(RTRIM(s.name)) +';'+CHAR(13)

FROM sysobjects s

left join sys.objects obj

on s.id = obj.object_id and s.name=obj.name

WHERE s.[type] = 'u' AND category = 0 ORDER BY s.[name]

--EXEC (@dropViews)

Print @SQL

go