Drop That Schema

, 2017-11-09 (first published: )

An often under utilized or maybe even mis-utilized feature of SQL Server is a database object schema. In the event of the latter, there is an occasional requirement for change. When this need arises, it can turn into a bit of a problem. That is of course if we are not well prepared. In this article, I am going to explore one possible change – the dropping of a schema.

What is a Schema?

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removes the tight coupling of database objects and owners to improve the security administration of database objects (Source: Technet).

Much like a skeleton is a distinct system that helps support the structural integrity of certain biological entities, a schema helps to support a distinct functioning and structure within a database. This is, of course, a very simplistic description and analogy, but it works.

Another way to look at a schema is almost like a blueprint of what has been or will be created within the database. Like many blueprints, a database schema can map out all sorts of “sub-systems” at our disposal within the database. Some of these may include functions, procedures, tables, views and so forth.

What happens when you try to take out the entire blueprint without understanding the relationship to all of the subsystems? In the case of the schema it just so happens that you will be presented with an error message informing you that you are trying to do something that is either ill-advised or not supported or both.

Here is an example of such an error.

Msg 3729, Level 16, State 1, Line 2
Cannot drop schema ‘Maintenance’ because it is being referenced by object ‘AutoStatsHistory’.

This error message proves to be helpful. I obviously have some objects bound to the schema that need to be blown away before I can drop the schema. If I only have a few objects, this may not be too terribly annoying to do one by one. But what if I have 100 or more objects? Now that becomes an entirely different story.

Drop that Schema!

I have run into this very issue where there are far too many objects in the schema to be able to drop one by one. Add to the problem that I am looking to do this via script. Due to the need to drop the schema and the (albeit self imposed) requirement of doing it via script, I came up with the following that will cover most cases that I have encountered.

DECLARE @schemaname VARCHAR(128) = 'scout';
IF ( SELECT OBJECT_ID('tempdb.dbo.#dropschema')
		DROP TABLE #dropschema;
SELECT s.name AS SchName
		, o.name AS ObjName
		, o.create_date
		, o.type_desc
		, o.type
		, o.parent_object_id
		, CASE	WHEN o.type IN ( 'F', 'D', 'UQ' )
				THEN 'ALTER TABLE [' + s.name + '].['
						+ OBJECT_NAME(o.parent_object_id)
						+ '] DROP CONSTRAINT [' + o.name + '];'
				WHEN o.type IN ( 'P', 'PC' )
				THEN 'DROP PROCEDURE [' + s.name + '].[' + o.name + '];'
				WHEN o.type IN ( 'FT', 'FN', 'TF', 'AF', 'FS', 'IF' )
				THEN 'DROP FUNCTION [' + s.name + '].[' + o.name + '];'
				WHEN o.type = 'V'
				THEN 'DROP VIEW [' + s.name + '].[' + o.name + '];'
				WHEN o.type = 'SO'
				THEN 'DROP SEQUENCE [' + s.name + '].[' + o.name + '];'
				WHEN o.type = 'U'
				THEN 'DROP TABLE [' + s.name + '].[' + o.name + '];'
				WHEN o.type = 'PG'
				THEN 'EXEC sp_control_plan_guide N''DROP'', N''' + o.name
						+ ''';'
			END AS 'DropText'
		, CASE	WHEN o.type IN ( 'P', 'FN', 'FT', 'TF', 'PC', 'FS', 'AF', 'IF' )
				THEN 1
				WHEN o.type = 'V' THEN 2
				WHEN o.type IN ( 'F', 'D' ) THEN 3
				WHEN o.type = 'SO' THEN 4
				WHEN o.type = 'UQ' THEN 5
				WHEN o.type = 'PG' THEN 6
				WHEN o.type = 'U' THEN 7
			END AS ProcessOrder
	INTO #dropschema
	FROM sys.objects o
		INNER JOIN sys.schemas s
			ON o.schema_id = s.schema_id
		--left outer join sys.default_constraints dc
		--	on o.object_id = dc.parent_object_id
	WHERE o.type NOT IN ( 'PK', 'IT', 'S' )
		AND s.name = @schemaname
	ORDER BY o.parent_object_id DESC
		, o.type;
				FROM #dropschema
				WHERE type IN ( 'PC', 'FS', 'AF', 'FT' ) )
		INSERT INTO #dropschema
				( SchName
					, ObjName
					, create_date
					, type_desc
					, type
					, parent_object_id
					, DropText
					, ProcessOrder
				SELECT '' AS ScheName
						, ass.name AS ObjName
						, ass.create_date
						, 'Assembly'
						, 'AS'
						, asm.assembly_id
						, 'DROP ASSEMBLY [' + ass.name + '];' AS DropText
						, 4 AS ProcessOrder
					FROM sys.assemblies ass
						INNER JOIN sys.assembly_modules asm
							ON ass.assembly_id = asm.assembly_id
						INNER JOIN sys.objects o
							ON asm.object_id = o.object_id;
INSERT INTO #dropschema
		( DropText
			, ProcessOrder
			, SchName
			, ObjName
			, create_date
			, parent_object_id
		( 'DROP SCHEMA ' + QUOTENAME(@schemaname) + ';'
			, 99
			, ''
			, ''
			, ''
			, ''
	FROM #dropschema
	ORDER BY ProcessOrder ASC;

And a sample of the output:

As you can see here in the output, I have set the script to generate a series of drop statements for each of the dependent objects within the schema. In addition, the drop statements are ordered to remove objects that may have dependencies on other objects first. A big key here is that this script does not drop the objects for you. Rather it just creates the scripts to do the work. You must review the output and then execute the scripts separately. That means you will be entirely responsible for the results.

Once all of the objects are out of the way, the last statement in the sequence is to finally drop the schema.

You may notice that there are a few things not included in this script. The most notable may be that the Service Broker related objects are not accounted for in this script. I leave that for a later revision.


Occasionally it becomes necessary to remove a schema from the database for one reason or another (e.g. somebody decided to do both Dev and Prod in the same database separated only by schemas). Dropping a schema can become a little bit of a pain without the right tools. This script will help get you on your way to a scriptable solution (to help with documentation of what was changed) and a little less pain while doing it.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads