I forcefully happen to write this post. At my work place, I don't have rights to create/drop the databases only DBAs have that privilege and process involved in that is also time consuming. Hence I have opportunity to create a SP to make it quicker and smarter. This SP has been created with the help of www. Basically, below Stored Procedure will deletes the all stored procedures (SP), views, functions, table constrains and finally tables from a database in a minute. you can alter this SP where in you can delete other objects under '-- you can add custom types here' section.
PS: be careful while executing this SP. It will completely revamp the whole database without warning.
CREATE PROCEDURE sp_EmptyDatabase AS
DECLARE @ObjectName VARCHAR(1000)
DECLARE @ObjectType VARCHAR(20)
DECLARE @SQLQuery NVARCHAR(4000)
DECLARE SPViews_CURSOR CURSOR FOR
SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS name, ROUTINE_TYPE AS xtype FROM INFORMATION_SCHEMA.ROUTINES
UNION
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS name, 'VIEW' AS xtype FROM INFORMATION_SCHEMA.VIEWS
OPEN SPViews_CURSOR
FETCH NEXT FROM SPViews_CURSOR INTO @ObjectName, @ObjectType
WHILE @@fetch_status = 0
BEGIN
--Deleting all the procedures
IF @ObjectType = 'PROCEDURE'
BEGIN
SET @SQLQuery = 'DROP PROCEDURE ' + @ObjectName
IF(@ObjectName!=OBJECT_NAME(@@PROCID) OR @ObjectName!='sp_EmptyDatabase')
BEGIN
EXEC sp_EXECutesql @SQLQuery
END
SET @SQLQuery = ' '
END
--Deleting all the functions
IF @ObjectType = 'FUNCTION'
BEGIN
SET @SQLQuery = 'DROP FUNCTION ' + @ObjectName
EXEC sp_EXECutesql @SQLQuery
SET @SQLQuery = ' '
END
--Deleting all the views
IF @ObjectType = 'VIEW'
BEGIN
SET @SQLQuery = 'DROP VIEW ' + @ObjectName
EXEC sp_EXECutesql @SQLQuery
SET @SQLQuery = ' '
END
FETCH NEXT FROM SPViews_CURSOR INTO @ObjectName, @ObjectType
END
CLOSE SPViews_CURSOR
DEALLOCATE SPViews_CURSOR
--Deleting all the constrains
DECLARE @CURSOR CURSOR
SET @CURSOR = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc.TABLE_NAME + '] DROP [' + rc.CONSTRAINT_NAME + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME =rc.CONSTRAINT_NAME
OPEN @CURSOR FETCH NEXT FROM @CURSOR INTO @SQLQuery
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC SP_EXECUTESQL @SQLQuery
FETCH NEXT FROM @CURSOR INTO @SQLQuery
END
CLOSE @CURSOR DEALLOCATE @CURSOR
GO
--Deleting all the tables
EXEC sp_MSFOREachTable 'DROP TABLE ?'
GO
References : http://blogs.msdn.com/b/patrickgallucci/archive/2008/04/29/how-to-drop-all-tables-all-views-and-all-stored-procedures-from-a-sql-2005-db.aspx
0d8aa664-5525-4642-96f9-6313a103897f|0|.0|27604f05-86ad-47ef-9e05-950bb762570c