Rui Romano's implementation of DROP SCHEMA CASCADE for MSSQL Server

dropschema.sql

/****** Object:  StoredProcedure [dbo].[DropSchema]    Script Date: 31/01/2013 16:51:00 ******/
/* https://ruiromanoblog.wordpress.com/2011/01/27/drop-a-sql-server-schema-and-all-objects-related/ */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[DropSchema]
GO
CREATE PROCEDURE [dbo].[DropSchema]
(
  @schemaName varchar(100)  
  ,@onlyObjects bit = 0
  ,@database varchar(200) = null
  ,@whatIf bit = 1
)
AS
BEGIN   
	SET NOCOUNT ON;
		
	if @database is null
	begin
		set @database = '[' + DB_NAME() + ']'
	end	
	
	-- I have to execute using dynamic SQL because there's no way of doing a Use [Database] dynamicaly :(
	
	declare @getDropCommandSql nvarchar(max)
	
	set @getDropCommandSql = 
	'
	use ' + @database + '
	
	set @dropSqlCommand = ''''
	
	-- Foreign Keys referencing a PK in the target schema
	
	select @dropSqlCommand = @dropSqlCommand
		+ ''ALTER TABLE [''+SCHEMA_NAME(fk.schema_id)+''].[''+OBJECT_NAME(fk.parent_object_id)+''] DROP CONSTRAINT [''+ fk.name
		+ '']'' + CHAR(13)
	from sys.foreign_keys fk
	join sys.tables t on t.object_id = fk.referenced_object_id
	where t.schema_id = schema_id(@schemaName)
		   and fk.schema_id <> t.schema_id
	order by fk.name desc
		 
	-- Default Constraints, Check Constraints and Foreign Keys

	select @dropSqlCommand = @dropSqlCommand
		+ ''ALTER TABLE [''+schema_name(t.schema_id)+''].[''+OBJECT_NAME(fk.parent_object_id)+''] DROP CONSTRAINT [''+ fk.[Name]
		+ '']'' + CHAR(13)
	from sys.objects fk
	  join sys.tables t on t.object_id = fk.parent_object_id
	where t.schema_id = schema_id(@schemaName)
	  and fk.type IN (''D'', ''C'', ''F'')
	   

	-- Drop remaining objects   
	
	SELECT @dropSqlCommand = @dropSqlCommand
		+ CASE 
			WHEN SO.type=''PK'' THEN ''ALTER TABLE [''+SCHEMA_NAME(SO.schema_id)+''].[''+OBJECT_NAME(SO.parent_object_id)+''] DROP CONSTRAINT [''+ SO.name + '']''
			WHEN SO.type=''U'' THEN ''DROP TABLE [''+SCHEMA_NAME(SO.schema_id)+''].[''+ SO.[Name] + '']''
			WHEN SO.type=''V'' THEN ''DROP VIEW  [''+SCHEMA_NAME(SO.schema_id)+''].[''+ SO.[Name] + '']''
			WHEN SO.type=''P'' THEN ''DROP PROCEDURE  [''+SCHEMA_NAME(SO.schema_id)+''].[''+ SO.[Name] + '']''         
			WHEN SO.type=''TR'' THEN ''DROP TRIGGER  [''+SCHEMA_NAME(SO.schema_id)+''].[''+ SO.[Name] + '']''
			WHEN SO.type  IN (''FN'', ''TF'',''IF'',''FS'',''FT'') THEN ''DROP FUNCTION  [''+SCHEMA_NAME(SO.schema_id)+''].[''+ SO.[Name] + '']''
		END
		+ CHAR(13)
	FROM SYS.OBJECTS SO
	WHERE SO.schema_id = schema_id(@schemaName)
	  AND SO.type IN (''PK'', ''FN'', ''TF'', ''TR'', ''V'', ''U'', ''P'')
	ORDER BY CASE WHEN type = ''PK'' THEN 1
		  WHEN type in (''FN'', ''TF'', ''P'',''IF'',''FS'',''FT'') THEN 2
		  WHEN type = ''TR'' THEN 3
		  WHEN type = ''V'' THEN 4
		  WHEN type = ''U'' THEN 5
		ELSE 6
		  END

	-- Drop the Schema
	
	if (@onlyObjects = 0)
	begin
		SELECT top 1 @dropSqlCommand = @dropSqlCommand
			+ ''DROP SCHEMA ['' + @schemaName + '']''
			+ CHAR(13)
		FROM SYS.OBJECTS SO
		WHERE SO.schema_id = schema_id(@schemaName)
	end
	
	if (@dropSqlCommand is not null and @dropSqlCommand <> '''')
		set @dropSqlCommand = ''USE '' + @database + char(13) + @dropSqlCommand				
	'	
		
	declare @dropSqlCommand nvarchar(max)
	
	exec sp_executesql @query = @getDropCommandSql 
		, @params = N'@schemaName varchar(100), @onlyObjects bit, @database varchar(200), @dropSqlCommand nvarchar(max) output' 			
		, @schemaName = @schemaName
		, @onlyObjects = @onlyObjects
		, @database = @database
		, @dropSqlCommand = @dropSqlCommand output
	
	if (@dropSqlCommand is null or @dropSqlCommand = '')
	begin
		raiserror ('Found no object for schema "%s" on database "%s".', 0, 1, @schemaName, @database)
		return
	end
	
	-- Executes the commands
	if (@whatIf = 1)
	begin	
		raiserror ('%s', 0, 1, @dropSqlCommand)
	end
	else
	begin		
		exec sp_executesql @query = @dropSqlCommand 	
	end	  

END