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.
USE DBA;
GO
DECLARE @schemaname VARCHAR(128) = 'scout';
IF ( SELECT OBJECT_ID('tempdb.dbo.#dropschema')
) IS NOT NULL
BEGIN
DROP TABLE #dropschema;
END;
SELECT s.name AS SchName
, o.name AS ObjName
, o.create_date
, o.type_desc
, o.type
, o.parent_object_id
, CASEWHEN 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'
, CASEWHEN 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;
IF EXISTS ( SELECT 1 / 0
FROM #dropschema
WHERE type IN ( 'PC', 'FS', 'AF', 'FT' ) )
BEGIN
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;
END;
INSERT INTO #dropschema
( DropText
, ProcessOrder
, SchName
, ObjName
, create_date
, parent_object_id
)
VALUES
( 'DROP SCHEMA ' + QUOTENAME(@schemaname) + ';'
, 99
, ''
, ''
, ''
, ''
);
SELECT *
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.
Conclusion
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.
