Wipe all objects in a database

Have you ever wanted to wipe out a whole database? All the objects?

Tested on SQL Server 2008 R2:

IF ( EXISTS ( SELECT * FROM sys.procedures p WHERE p.object_id = OBJECT_ID('[dbo].[ResetWarehouse]') ) )
DROP PROCEDURE [dbo].[ResetWarehouse];
GO

CREATE PROCEDURE [dbo].[ResetWarehouse] (
  @booDebug BIT = 0
)
AS
SET NOCOUNT ON;
DECLARE @svr SYSNAME = @@SERVERNAME;
IF ( @svr != 'YOUR_DEV_SERVER' )
BEGIN
  RAISERROR('WRONG SERVER: %s IS NOT YOUR_DEV_SERVER',25,1,@svr) WITH LOG;
  RETURN -1;
END

-- =============================================
-- Author: Brad Simpson
-- Create date: 2008-08-25@10:24
-- Description:	clean up
-- =============================================

SET @booDebug = COALESCE(@booDebug,0);
DECLARE @SQL as NVARCHAR(max);
DECLARE @tblObjects TABLE (
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
  ,ObjectId INT NOT NULL
  ,SchemaName SYSNAME NOT NULL
  ,ObjectName SYSNAME NOT NULL
  ,ObjectType SYSNAME NOT NULL
  ,ObjectIdParent INT NULL
  ,SchemaParent SYSNAME NULL
  ,ObjectParent SYSNAME NULL
  ,ObjectTypeParent SYSNAME NULL
  ,CountLoops INT NOT NULL DEFAULT(0)
);

WITH typeDesc ( OrderBy,type_desc ) AS (
SELECT 01,'FOREIGN_KEY_CONSTRAINT'
UNION ALL SELECT 02,'UNIQUE_CONSTRAINT'
UNION ALL SELECT 03,'DEFAULT_CONSTRAINT'
UNION ALL SELECT 04,'PRIMARY_KEY_CONSTRAINT'
UNION ALL SELECT 05,'SQL_INLINE_TABLE_VALUED_FUNCTION'
UNION ALL SELECT 06,'SQL_SCALAR_FUNCTION'
UNION ALL SELECT 07,'SQL_TABLE_VALUED_FUNCTION'
UNION ALL SELECT 08,'SQL_STORED_PROCEDURE'
UNION ALL SELECT 09,'USER_TABLE'
UNION ALL SELECT 10,'VIEW'
)
INSERT INTO @tblObjects ( [ObjectId],[SchemaName],[ObjectName],[ObjectType],[ObjectIdParent],[SchemaParent],[ObjectParent],[ObjectTypeParent] )
SELECT
   ObjectId = o.object_id
  ,SchemaName = s.name
  ,ObjectName = o.name
  ,ObjectType = o.type_desc
  ,ObjectIdParent = po.object_id
  ,SchemaParent = ps.name
  ,ObjectParent = po.name
  ,ObjectTypeParent = po.type_desc
FROM
  sys.schemas s
  INNER JOIN sys.objects o ON s.schema_id = o.schema_id
  LEFT OUTER JOIN sys.objects po ON o.parent_object_id != 0 AND o.parent_object_id = po.object_id
  LEFT OUTER JOIN sys.schemas ps ON po.schema_id = ps.schema_id
  INNER JOIN typeDesc td ON o.type_desc = td.type_desc
WHERE
  s.name NOT IN ('dbo','sys','INFORMATION_SCHEMA')
  AND NOT ( s.name = 'dbo' AND o.name = 'ResetWarehouse' AND o.type_desc = 'SQL_STORED_PROCEDURE' )
ORDER BY td.OrderBy,o.type_desc,s.name,o.name;

DECLARE @ID INT;
DECLARE @ObjectId INT;
DECLARE @SchemaName SYSNAME;
DECLARE @ObjectName SYSNAME;
DECLARE @ObjectType SYSNAME;
DECLARE @ObjectIdParent INT;
DECLARE @SchemaParent SYSNAME;
DECLARE @ObjectParent SYSNAME;
DECLARE @ObjectTypeParent SYSNAME;
WHILE ( EXISTS ( SELECT * FROM @tblObjects ) )
BEGIN
  SELECT TOP(1)
     @ID = o.ID
    ,@SQL = ''
    ,@ObjectId = o.ObjectId
    ,@SchemaName = o.SchemaName
    ,@ObjectName = o.ObjectName
    ,@ObjectType = o.ObjectType
    ,@ObjectIdParent = o.ObjectIdParent
    ,@SchemaParent = o.SchemaParent
    ,@ObjectParent = o.ObjectParent
    ,@ObjectTypeParent = o.ObjectTypeParent
  FROM @tblObjects o
  ORDER BY o.CountLoops,o.ID;

  --IF ( @ObjectIdParent IS NULL )
  --  RAISERROR('Object: %s.%s: %s',0,1,@SchemaName,@ObjectName,@ObjectType) WITH NOWAIT;
  --ELSE
  --  RAISERROR('Object: %s.%s: %s ON %s.%s: %s',0,1,@SchemaName,@ObjectName,@ObjectType,@SchemaParent,@ObjectParent,@ObjectTypeParent) WITH NOWAIT;

  SELECT @SQL = CASE @ObjectType
    WHEN 'DEFAULT_CONSTRAINT'     THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'FOREIGN_KEY_CONSTRAINT' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'PRIMARY_KEY_CONSTRAINT' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'
    WHEN 'UNIQUE_CONSTRAINT'      THEN 'ALTER TABLE ' + QUOTENAME(@SchemaParent) + '.' + QUOTENAME(@ObjectParent) + ' DROP CONSTRAINT ' + QUOTENAME(@ObjectName) + ';'

    WHEN 'SQL_INLINE_TABLE_VALUED_FUNCTION' THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'SQL_SCALAR_FUNCTION'              THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'SQL_TABLE_VALUED_FUNCTION'        THEN 'DROP FUNCTION ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'

    WHEN 'SQL_STORED_PROCEDURE' THEN 'DROP PROCEDURE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'VIEW'                 THEN 'DROP VIEW '      + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    WHEN 'USER_TABLE'           THEN 'DROP TABLE '     + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';'
    ELSE NULL END

  UPDATE o
  SET o.CountLoops = COALESCE(o.CountLoops,1) + 1
  FROM @tblObjects o
  WHERE o.ID = @ID;

  SET @SQL = NULLIF(LTRIM(RTRIM(@SQL)),'');
  IF ( @SQL IS NOT NULL )
  BEGIN
    BEGIN TRY
      --RAISERROR('%s',0,1,@SQL) WITH NOWAIT;
      IF ( @booDebug = 0 ) EXEC sp_executesql @SQL;
      DELETE o FROM @tblObjects o WHERE o.ID = @ID;
    END TRY
    BEGIN CATCH
      SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
      RETURN 0;
    END CATCH
  END  
  DELETE FROM o FROM @tblObjects o WHERE o.CountLoops > 100;
END
GO