Dropping all objects in a schema

I had the need to drop all objects that I created in a specific schema as part of a unit test. Then I decided to test my script on the Adventure Works 2014 sample database and I got a couple of errors. Some of these errors were that there were foreign key constraints from a different schema pointing to a table in this schema. I also got an error complaining about an XML SCHEMA collection living in my schema.

If you want to follow along by running the scripts, first download the Adventure Works 2014 sample database

After you are done downloading the database, create a folder named training on your C drive.
Open up SSMS, open a query window, run the following script

USE [master]

--DROP DATABASE AdventureWorks2014
GO

RESTORE DATABASE AdventureWorks2014
FROM disk= 'C:\Training\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_data' TO 'C:\Training\AdventureWorks2014.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Training\AdventureWorks2014.ldf'
,REPLACE, stats =20

Now that the DB is available, we can look at what the script looks like. Connect to the database you just created and open up a new query window. Before running the script make sure that you are in text mode, just hit CTRL + T in your  SSMS query window

Here is what the script looks like

   SET NOCOUNT ON
   DECLARE  @SchemaName NVARCHAR(100) = 'HumanResources'


   SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id))
   +'.'+ QUOTENAME(OBJECT_NAME(s.parent_object_id))
   + ' DROP CONSTRAINT ' +  QUOTENAME(s.name)   
   FROM sys.foreign_keys s
   JOIN sys.tables t on s.parent_object_id = t.object_id
   JOIN sys.tables t2 on s.referenced_object_id = t2.object_id
    WHERE t2.schema_id = SCHEMA_ID(@SchemaName)

    SELECT 'DROP ' +
                    CASE WHEN type IN ('P','PC') THEN 'PROCEDURE'
                         WHEN type =  'U' THEN 'TABLE'
                         WHEN type IN ('IF','TF','FN') THEN 'FUNCTION'
                         WHEN type = 'V' THEN 'VIEW'
                     END +
                   ' ' +  QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)  
                   FROM sys.objects
             WHERE schema_id = SCHEMA_ID(@SchemaName)
    AND type IN('P','PC','U','IF','TF','FN','V')
    ORDER BY  CASE WHEN type IN ('P','PC') THEN 4
                         WHEN type =  'U' THEN 3
                         WHEN type IN ('IF','TF','FN') THEN 1
                         WHEN type = 'V' THEN 2
                       END


  SELECT 'DROP XML SCHEMA COLLECTION '
  + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)
  FROM sys.xml_schema_collections
  WHERE schema_id = SCHEMA_ID(@SchemaName)

  SELECT 'DROP SCHEMA ' + QUOTENAME(@SchemaName)

As you can see, there are 4 things going on here.

1. Remove the constraints
2. Drop Views, Function, Stored Procedures and Tables, drop functions and views first, procs last
3. Drop XML Schema Collections
4. Drop the Schema

Of course if you have a view in  another database or schema referencing your objects inside this schema you will have problems...I will get to this later


Running that script will give you the following output

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID]
ALTER TABLE [Production].[Document] 
DROP CONSTRAINT [FK_Document_Employee_Owner]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[EmployeePayHistory] 
DROP CONSTRAINT [FK_EmployeePayHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[JobCandidate] 
DROP CONSTRAINT [FK_JobCandidate_Employee_BusinessEntityID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] 
DROP CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID]
ALTER TABLE [Sales].[SalesPerson] 
DROP CONSTRAINT [FK_SalesPerson_Employee_BusinessEntityID]


DROP VIEW [HumanResources].[vEmployee]
DROP VIEW [HumanResources].[vEmployeeDepartment]
DROP VIEW [HumanResources].[vEmployeeDepartmentHistory]
DROP VIEW [HumanResources].[vJobCandidate]
DROP VIEW [HumanResources].[vJobCandidateEmployment]
DROP VIEW [HumanResources].[vJobCandidateEducation]
DROP TABLE [HumanResources].[Shift]
DROP TABLE [HumanResources].[Department]
DROP TABLE [HumanResources].[Employee]
DROP TABLE [HumanResources].[EmployeeDepartmentHistory]
DROP TABLE [HumanResources].[EmployeePayHistory]
DROP TABLE [HumanResources].[JobCandidate]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]


DROP XML SCHEMA COLLECTION [HumanResources].[HRResumeSchemaCollection]


DROP SCHEMA [HumanResources]

Run that and it should all run fine, the schema should be gone

Now change @SchemaName NVARCHAR(100) = 'HumanResources' to @SchemaName NVARCHAR(100) = 'Sales'

Run the script again, run the output from the script

Finally change the variable to Person @SchemaName NVARCHAR(100) = 'Person'

Run the script, run the output.  All 3 schemas we specified should be gone now



Now restore the database again, This time run the code to remove the Sales Schema, Then run the code to remove the Person Schema.

Here is the error you will receive


Msg 6328, Level 16, State 1, Line 39
Specified collection 'AdditionalContactInfoSchemaCollection' cannot be dropped because it is used by object 'HumanResources.vEmployee'.

However, it is really used by the Person.Person table which we just dropped. In this case you will need to drop the view. If you have stuff like this in the database, you probably don't want to just start dropping objects in another schema that point to objects in your schema

I am not having these kind of issues so the code is good enough for me to wipe out objects that have been created as part of my unit testing