Transact SQL – Drop Objects in Specific Schema

Background

Need a quick script that can delete all objects in a specific schema.

For example, we have a backup schema and in time, we have so many objects in it and no longer need it, as will be starting over again.

Code

dbo.sp_cleanUpObjectsInSchema


use master
go

if object_id('dbo.sp_cleanUpObjectsInSchema') is null
begin

	exec('create procedure dbo.sp_cleanUpObjectsInSchema as select 1/0 as [shell] ')

end
go

alter procedure dbo.sp_cleanUpObjectsInSchema
(
	  @schema			 sysname
	, @scriptOnly		 bit = 1
	, @allowDataLoss	 bit = 0
	, @dropSchemaItself  bit = 0
)
as
begin

	set XACT_ABORT on;
	set nocount on;

	declare @schemaID int

	declare @id int
	declare @idMax int 

	declare @sqlFormatSchemaDoesNotExist varchar(4000)
	declare @sqlFormatDropSchema		 varchar(4000)

	declare @sql  varchar(4000)
	declare @sqlDropSchema  varchar(4000)

	declare @tblObjectType TABLE
	(
		  [type]		char(2)
		, [typeDesc]	sysname	
	)

	declare @tbl TABLE
	(
		  [id]				int not null identity(1,1)
		, [schema]			sysname
		, [object]			sysname
		, [objectType]		varchar(2)
		, [objectTypeDesc]  sysname
		, [dropType]		sysname  null
		, [sqlClause]  
			as 'drop'
					+ ' '
					+ [dropType]
					+ ' '
					+ quoteName([schema])
					+ '.'
					+ quoteName([object])

	)

	declare @log varchar(8000)

	/*
		Prepare Format Strings
	*/
	set @sqlFormatSchemaDoesNotExist
		= 'Schema, %s, does not exist! '

	set @sqlFormatDropSchema = 'if schema_id(''%s'') is not null begin drop schema [%s] end '

	
	/*
		Map Object Type ID to Object Type Name
			sys.objects
			https://msdn.microsoft.com/en-us/library/ms190324.aspx
	*/
	insert into @tblObjectType
	(
		  [type]
		, [typeDesc]
	)
	select 'P', 'Procedure'
	union
	select 'V', 'View'
	union
	select 'T', 'Table'
	union
	select 'FN', 'Function'
	union
	select 'IF', 'Function'
	union
	select 'TR', 'Trigger'

	/*
		Get Schema ID
	*/
	set @schemaID = schema_id(@schema)

	/*
		If schema id is null, then schema not found
			Let us get out of here!
	*/
	if (@schemaID is null)
	begin
		
		exec master.dbo.xp_sprintf 
			  @log output
			, @sqlFormatSchemaDoesNotExist
			, @schema

		print @log

		return

	end
	
	insert into @tbl
	(
		  [schema]
		, [object]
		, [objectType]
		, [objectTypeDesc]
		, [dropType]
	)
	select 
		    [schema]
				 = schema_name
					(
						tblO.schema_id
					)
		  , tblO.name
		  ,	tblO.type
		  ,	tblO.type_desc
		  , tblOT.[typeDesc]
		
	from   sys.objects tblO

	inner join @tblObjectType tblOT

			on tblO.[type] = tblOT.[type] Collate Database_Default 

	where  tblO.[schema_id] =@schemaID

	and
		(

			-- Handle Allow Data Loss
			(
				   (@allowDataLoss = 1)

				or (
							(@allowDataLoss = 0)
						and	(
								tblO.[type] not in ('U')
							)
					)
			)

		)

	ORDER BY
			tblO.name

	set @id = 1
	set @idMax = ( select max([id]) from @tbl )

	/*
		Iterate List
	*/
	while (@id <= @idMax)
	begin

		/*
			Get SQL
		*/
		select @sql  = sqlClause
		from   @tbl
		where  [id] = @id

		/*
			Display SQL
		*/
		print @sql

		if (@scriptOnly = 0)
		begin

			/*
				If we are not in ScriptOnly mode, execute sql
			*/
			exec(@sql)

		end

		set @id = @id + 1

	end

	if (@dropSchemaItself = 1)
	begin

		/*
			Prepare drop schema command
		*/
		exec master.dbo.xp_sprintf 
				  @sqlDropSchema output
				, @sqlFormatDropSchema
				, @schema
				, @schema

		print @sqlDropSchema

		if (@scriptOnly = 0)
		begin

			exec(@sqlDropSchema)

		end


	end

end
go

EXEC sys.sp_MS_marksystemobject 'dbo.sp_cleanUpObjectsInSchema'
go

Invokation

Code


	use [csBETA]
	go

	-- select * from sys.schemas
	declare @schema sysname
	declare @scriptOnly bit
	declare @allowDataLoss bit
	declare @dropSchemaItself bit

	set @scriptOnly = 1
	set @schema = 'dbBackup'
	set @allowDataLoss = 0
	set @dropSchemaItself = 0

	begin tran

		exec dbo.sp_cleanUpObjectsInSchema
			  @schema = @schema
			, @scriptOnly = @scriptOnly
			, @allowDataLoss = @allowDataLoss
			, @dropSchemaItself = @dropSchemaItself 

	rollback tran

Output


drop Procedure [dbBackup].[FindStudentsTXReport]
drop Procedure [dbBackup].[GetAZTSRemittanceReport.2015]
drop Procedure [dbBackup].[GetHighSchoolCreditsReport]
drop Procedure [dbBackup].[GetHighSchoolCreditsReport.20151125]
drop Procedure [dbBackup].[GetNotPrintedCAOT.201512030544PM]
drop Procedure [dbBackup].[GetNotPrintedCOOT]
drop Procedure [dbBackup].[GetNotPrintedMNOT]
drop Procedure [dbBackup].[GetStudentFeedbackByRegionReport.20151203]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s