Cacher is the code snippet organizer for pro developers

DECLARE @dSQL NVARCHAR(MAX), @schemaName SYSNAME = 'backjobs'
;WITH commands AS (
SELECT 'DROP '+ CASE WHEN o.type_desc LIKE '%TABLE'      THEN 'TABLE [' +s.name+'].['+o.name+']'
                     WHEN o.type_desc LIKE '%VIEW'       THEN 'VIEW [' +s.name+'].['+o.name+']'
			         WHEN o.type_desc LIKE '%FUNCTION'   THEN 'FUNCTION [' +s.name+'].['+o.name+']'
			         WHEN o.type_desc LIKE '%TRIGGER'    THEN 'TRIGGER [' +s.name+'].['+o.name+'] ON ['+ps.name+'].['+p.name+']'
			         WHEN o.type_desc LIKE '%PROCEDURE'  THEN 'PROCEDURE [' +s.name+'].['+o.name+']'
			         WHEN o.type_desc LIKE '%CONSTRAINT' THEN 'CONSTRAINT [' +s.name+'].['+o.name+'] ON ['+ps.name+'].['+p.name+']'
					 WHEN o.type_desc LIKE '%QUEUE' THEN 'QUEUE [' +s.name+'].['+o.name+']'
			         ELSE '??'
       END AS cmd, ROW_NUMBER() OVER (ORDER BY p.parent_object_id DESC) AS rn
  FROM sys.objects o
    INNER JOIN sys.schemas s
	  ON o.schema_id = s.schema_id
	LEFT OUTER JOIN sys.objects p
	  ON o.parent_object_id = p.object_id
	LEFT OUTER JOIN sys.schemas ps
	  ON p.schema_id = ps.schema_id
 WHERE s.name = @schemaName
), rCTE AS (
SELECT CAST(cmd AS NVARCHAR(MAX)) AS cmd, rn
  FROM commands
 WHERE rn = 1
UNION ALL
SELECT CAST(r.cmd AS NVARCHAR(MAX)) +CHAR(10)+ CAST(a.cmd AS NVARCHAR(MAX)), a.rn
  FROM rCTE r
    INNER JOIN commands a
	  ON r.rn + 1 = a.rn
)

SELECT @dSQL = cmd
  FROM rCTE 
 WHERE rn = (SELECT MAX(rn) FROM commands)

 PRINT @dSQL
 --EXEC @dSQL

SET @dSQL = 'DROP SCHEMA ['+@schemaName+']'
 PRINT @dSQL
 --EXEC @dSQL