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