I have spent almost 3 hours to complete this script and test it for couple of sample scenarios during this weekend (31/01/10). It drops all objects of a schema and then drops the schema itself. The script automatically takes care of all the object dependencies with in the schema by dropping all of its objects in a specific order that will resolve the dependency issues.
You can download the SP created using the script from here (MS Word Document) or use this Google Doc link to view the script ( Thanks to Elias for the link ).
The stored procedure takes two parameters; the @schemaname and the work test. Use @worktest equal to ‘t’ to print all the drop statements without executing them or specify anything else to execute the drop operations. The default option is ‘w’ i.e. it drops all the objects in the specified schema.
EXEC CleanUpSchema 'MySchema', 't' -- debug GO EXEC CleanupSchema 'MySchema', 'w' -- work for me
Below are the known limitations of the script
- It can not drop a PK table in the schema with an XML index or Spatial index defined
- It can not drop the schema which is referred by a XML schema collection
Please let me know if you find any more issues with the script. I will list all of them here for others reference and will fix them to improve it further.
Thanks to the readers for the help in improving the script. Now it handles UDFs, Synonyms and SEQUENCE objects too.
Hope it helps
– Ranjith
Thanks ranjith! you are a life saver…
i was having trouble with opening the word file, so i went the route of uploading it to google docs and letting google convert it for me.
anyone who needs an online version of the TSQ, you can find it here:
docs.google.com/View?id=dgwxbk97_155c5dpjqcc
Elias,
Good to hear that it helped. And Thanks for converting it to Google doc and posting the link here. I have updated the main post to use the Google link as well.
when selecting a function the test should be IN (‘FN’, ‘IF’, ‘TF’, ‘FS’, ‘FT’)
Thanks Valeriu. I have modified the word document to cover the other object types too. (IF – SQL Inline Table Valued Function, FS – CLR Scalar Function, FT – CLR Table Valued Function). I dont have permission to update the Google Doc, Need to check with Elias to do the same.
Looks like it doesn’t account for spatial index.
Msg 3734, Level 16, State 1, Line 1
Could not drop the primary key constraint ‘R2_pk’ because the table has an XML or spatial index.
Otherwise works good…
Yes Rich. Currently it can not handle the spatial indexes and XML indexes. Thanks for commenting.
I would like to include this script in our package, as we make extensive use of schemas to divide up the namespace. Are there any particular terms for doing this?
thanks,
Peter Hussey
LabKey Software
(and former SQL Server group program manager)
Hi Peter,
Thanks for asking and I am glad to hear that its useful to you guys. Please use it and I do not have any specific terms in this regard.
Thanks,
Ranjith.
Hi – very useful script and great blog!.
I’m going to incorporate it, if that’s ok, into a data warehouse staging project I’m working on. We read data from some disparate systems which we can throw away once it’s in our warehouse tables. The other systems use some funky ODBC connections – using linked servers in SQL Server was a bit slow so I was hoping to make tables in a schema, populate those tables using SSIS, massage the data into our warehouse, and then throw away the temporary schema. I could just use a scratch database but sometimes there will be multiple databases on the one server (when I’m testing for example) each of which would need its own scratch area.
I also already have the massage code in T-SQL so I don’t want to recode that logic in SSIS – so the scratch schema+tables is ideal for the lazy developer in me!
I’m modifying it slightly to indicate whether or not it should print status and, if the schema does not exist, to do nothing rather than throw an error 🙂
Good to know that it is useful and saved some development time. And thanks for commenting.
Hi – very useful script
Thanks
Very useful and live saving script..
Thanks,
Rod
Thanks Rod
Have used this twice now when 3rd party app fails to completely execute the T-SQL to create a blank db schema for a new account.
This script works great to quickly purge the half-created schema so I can run the full 3rd party creation script without reviewing 5 screens of errors about already existing objects.
Thanks for sharing!
Thanks Mark !!! Its good to hear that
Thanks Ranjith you’ve saved me a bunch of work, if only i’d come across it earlier !
Cheers, Mark.
Excellent! Thx for sharing!
Had a slight issue with osme of the names SQL Server assigns to the primary/foreign key objects it creates, in that it sometimes uses dashes. When I try to drop the schema, the individual DROP commands for these objects fails because the schema name and object names are not contained in square brackets. I fixed that, and am including the altered code here in case you want to incorporate it back into your doc:
BEGIN
declare @SQL varchar(4000)
declare @msg varchar(500)
IF OBJECT_ID(‘tempdb..#dropcode’) IS NOT NULL DROP TABLE #dropcode
CREATE TABLE #dropcode
(
ID int identity(1,1)
,SQLstatement varchar(1000)
)
— removes all the foreign keys that reference a PK in the target schema
SELECT @SQL =
‘select
” ALTER TABLE [”+SCHEMA_NAME(fk.schema_id)+”].[”+OBJECT_NAME(fk.parent_object_id)+”] DROP CONSTRAINT [”+ fk.name + ”]”
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’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
— drop all default constraints, check constraints and Foreign Keys
SELECT @SQL =
‘SELECT
” ALTER TABLE [”+schema_name(t.schema_id)+”].[”+OBJECT_NAME(fk.parent_object_id)+”] DROP CONSTRAINT [”+ fk.[Name] + ”]”
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”)’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
— drop all other objects in order
SELECT @SQL =
‘SELECT
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
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’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
DECLARE @ID int, @statement varchar(1000)
DECLARE statement_cursor CURSOR
FOR SELECT SQLStatement
FROM #dropcode
ORDER BY ID ASC
OPEN statement_cursor
FETCH statement_cursor INTO @statement
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @WorkTest = ‘t’ PRINT (@statement)
ELSE
BEGIN
PRINT (@statement)
EXEC(@statement)
END
FETCH statement_cursor INTO @statement
END
CLOSE statement_cursor
DEALLOCATE statement_cursor
IF @WorkTest = ‘t’ PRINT (‘DROP SCHEMA [‘+@SchemaName + ‘]’)
ELSE
BEGIN
PRINT (‘DROP SCHEMA [‘+@SchemaName+’]’)
EXEC (‘DROP SCHEMA [‘+@SchemaName+’]’)
END
PRINT ‘——- ALL – DONE ——-‘
END
Couple questions…
1) M$ suggests migrating to using the INFORMATION_SCHEMA views in lieu of sys.ojects ??
2) Instead of ap/pre pending []’s.. Much better to use QuoteName ?
David, Thanks for commenting and I will add this fix to the doc
Really useful. Thank you Ranjith!
Hi Thank you for this.
I am using external CLR regex methods contained in Assembly (CLR) stored-procedures (sys.objects type ‘PC’.
I amended your script slightly to accomodate these types of procedure.
My changes were
Line added:
WHEN SO.type=”PC” THEN ” DROP PROCEDURE ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
After
WHEN SO.type=”P” THEN ” DROP PROCEDURE ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
Line AND SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”)
changed to:
AND SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”, ”PC”)
and line
WHEN type in (”FN”, ”TF”, ”P”,”IF”,”FS”,”FT”) THEN 2
Changed to:
WHEN type in (”FN”, ”TF”, ”P”,”PC”,”IF”,”FS”,”FT”) THEN 2
Thanks
Jude Wood
Thanks Jude, I will add this change
Cannot drop schema ‘hcprod’ because it is being referenced by object ‘External’.
I am getting this error. What can be the cause?
Thanks. big time saver. works great.
Good to hear that 🙂
It is failing for me because of SYS.OJBECTS. I think it wants to use INFORMATION.SCHEMA instead. INFORMATION.SCHEMA has a different layout so it will take me a little time to rewrite. Has anyone else had this issue?
What version of SQL server are you using? And do you have access to view the sys.objects on server?
if its failing at the SYS.OBJECTS line it is probably because of the installation is case sensitive, I have corrected the problem, please use the latest script.
Thanks Krunoslav for pointing this out.
Thanks a lot!!! Very good!!!
Hi, Ranjith. Thanks for this – a very useful script.
It didn’t fully work on some of the schemas I tried to delete with it and I made some changes (below). In summary:
1) Added support for user defined types (both data and table)
2) Added support for synonyms
3) Reordered main delete so that functions (and sprocs) are deleted *after* tables etc. (this is because some of my tables had calculated columns that referenced functions).
I hope you find this useful.
Cheers,
Wayne
/********************************************************
COPYRIGHTS http://www.ranjithk.com
*********************************************************/
CREATE PROCEDURE CleanUpSchema
(
@SchemaName varchar(100)
,@WorkTest char(1) = ‘w’ — use ‘w’ to work and ‘t’ to print
)
AS
/*—————————————————————————————–
Author : Ranjith Kumar S
Date: 31/01/10
Description: It drop all the objects in a schema and then the schema itself
Limitations:
1. If a table has a PK with XML or a Spatial Index then it wont work
(workaround: drop that table manually and re run it)
2. If the schema is referred by a XML Schema collection then it wont work
If it is helpful, Please send your comments ranjith_842@hotmail.com or visit
http://www.ranjithk.com
——————————————————————————————-*/
BEGIN
declare @SQL varchar(4000)
declare @msg varchar(500)
IF OBJECT_ID(‘tempdb..#dropcode’) IS NOT NULL DROP TABLE #dropcode
CREATE TABLE #dropcode
(
ID int identity(1,1)
,SQLstatement varchar(1000)
)
— removes all the foreign keys that reference a PK in the target schema
SELECT @SQL =
‘select
” ALTER TABLE
”+SCHEMA_NAME(fk.schema_id)+”.”+OBJECT_NAME(fk.parent_object_id)+” DROP
CONSTRAINT ”+ fk.name
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’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
— drop all default constraints, check constraints and Foreign Keys
SELECT @SQL =
‘SELECT
” ALTER TABLE
”+schema_name(t.schema_id)+”.”+OBJECT_NAME(fk.parent_object_id)+” DROP
CONSTRAINT ”+ fk.[Name]
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”)’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
— drop various other objects in order
SELECT @SQL =
‘SELECT
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=”SN” THEN ” DROP SYNONYM
”+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
FROM SYS.OBJECTS SO
WHERE SO.schema_id = schema_id(”’+ @SchemaName +”’)
AND SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”, ”SN”)
ORDER BY CASE WHEN type = ”PK” THEN 1
WHEN type = ”TR” THEN 2
WHEN type = ”V” THEN 3
WHEN type = ”U” THEN 4
WHEN type in (”FN”, ”TF”, ”P”,”IF”,”FS”,”FT”) THEN 5
ELSE 6
END’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
— drop user-defined types
SELECT @SQL =
‘SELECT
” DROP TYPE ”+SCHEMA_NAME(ST.schema_id)+”.”+ ST.[Name]
FROM SYS.TYPES ST
WHERE ST.schema_id = schema_id(”’+ @SchemaName +”’)
AND ST.user_type_id > 256
ORDER BY ST.is_table_type DESC’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
DECLARE @ID int, @statement varchar(1000)
DECLARE statement_cursor CURSOR
FOR SELECT SQLStatement
FROM #dropcode
ORDER BY ID ASC
OPEN statement_cursor
FETCH statement_cursor INTO @statement
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @WorkTest = ‘t’ PRINT (@statement)
ELSE
BEGIN
PRINT (@statement)
EXEC(@statement)
END
FETCH statement_cursor INTO @statement
END
CLOSE statement_cursor
DEALLOCATE statement_cursor
IF @WorkTest = ‘t’ PRINT (‘DROP SCHEMA ‘+@SchemaName)
ELSE
BEGIN
PRINT (‘DROP SCHEMA ‘+@SchemaName)
EXEC (‘DROP SCHEMA ‘+@SchemaName)
END
PRINT ‘——- ALL – DONE ——-‘
END
Very usefull script! Thank you.
It would be a nice addition if the script used the QUOTENAME() function for the outer dynamic sql code generating the inner dynamic sql code enabling it to handle odd object names.
But it was not a problem adding this on my own and the script works very well.
I’m impressed, worked perfectly.
Thanks Ranjith,
Best regards
(from rainy Budapest)
Zsolt
Thanks! Worked like a charm!
Thanks for sharing your efforts. Does this handle User-Defined Table Types? I got a DB with many schema and not able to delete one because it contains UDTT. Let me know if there were any issues when you were developing it.
Very Nice! Much appreciated!
brilliant, appreciated.
Thank you so much.!!! This is great and Work fine!!!
Hello
it does not work for UDT and sequences
Updated to work for synonyms, udfs, sequences. Added arg to optionally drop the schema, or just clean the objects.
CREATE PROCEDURE dbAdmin.CleanUpSchema
(
@SchemaName varchar(100)
,@WorkTest char(1) = ‘w’ — use ‘w’ to work and ‘t’ to print
,@dropSchema BIT
)
AS
BEGIN
declare @SQL varchar(4000)
declare @msg varchar(500)
IF OBJECT_ID(‘tempdb..#dropcode’) IS NOT NULL DROP TABLE #dropcode
CREATE TABLE #dropcode
(
ID int identity(1,1)
,SQLstatement varchar(1000)
)
— removes all the foreign keys that reference a PK in the target schema
SELECT @SQL =
‘select
” ALTER TABLE ”+SCHEMA_NAME(fk.schema_id)+”.”+OBJECT_NAME(fk.parent_object_id)+” DROP CONSTRAINT ”+ fk.name
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’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
— drop all default constraints, check constraints and Foreign Keys
SELECT @SQL =
‘SELECT
” ALTER TABLE ”+schema_name(t.schema_id)+”.”+OBJECT_NAME(fk.parent_object_id)+” DROP CONSTRAINT ”+ fk.[Name]
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”)’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
— drop all other objects in order
SELECT @SQL =
‘SELECT
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=”SN” THEN ” DROP SYNONYM ”+SCHEMA_NAME(SO.schema_id)+”.”+ SO.[Name]
WHEN SO.type=”SO” THEN ” DROP SEQUENCE ”+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
FROM sys.objects SO
WHERE SO.schema_id = schema_id(”’+ @SchemaName +”’)
AND SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”,”SN”,”IF”,”SO”)
ORDER BY CASE WHEN type = ”PK” THEN 1
WHEN type = ”SN” THEN 2
WHEN type in (”FN”, ”TF”, ”P”,”IF”,”FS”,”FT”) THEN 3
WHEN type = ”TR” THEN 4
WHEN type = ”V” THEN 5
WHEN type = ”U” THEN 6
ELSE 7
END’
IF @WorkTest = ‘t’ PRINT (@SQL )
INSERT INTO #dropcode
EXEC (@SQL)
DECLARE @ID int, @statement varchar(1000)
DECLARE statement_cursor CURSOR
FOR SELECT SQLstatement
FROM #dropcode
ORDER BY ID ASC
OPEN statement_cursor
FETCH statement_cursor INTO @statement
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @WorkTest = ‘t’ PRINT (@statement)
ELSE
BEGIN
PRINT (@statement)
EXEC(@statement)
END
FETCH statement_cursor INTO @statement
END
CLOSE statement_cursor
DEALLOCATE statement_cursor
IF @dropSchema = 1
BEGIN
IF @WorkTest = ‘t’ PRINT (‘DROP SCHEMA ‘+@SchemaName)
ELSE
BEGIN
PRINT (‘DROP SCHEMA ‘+@SchemaName)
EXEC (‘DROP SCHEMA ‘+@SchemaName)
END
END
PRINT ‘——- ALL – DONE ——-‘
END
GO
Thanks !!! I have modified the script to work for Synonyms, and UDFs
April, 2016. Still a life saver. Thanks!
Feels good to know that Ortiz !!!!…. 🙂
Pretty neat, thanks! I notice you have to create and run the proc in the DB of choice. You can’t stash it in master and operate on schemas in other DBs. Guess it’s tough to move around within dynamic SQL.
Hi Ranjith, would you be so kind and attribute a specific license to your script release, e.g. MIT or BSD. Thanks.
Matthew – Please feel free to use it as the way you want and would appreciate if you can give credit !!!
This came in handy for me as well. Though one of my tables was named Order. So I had to modify the script to wrap the object names in brackets.
Thanks!
Hi Ranjith,
Just thought I’d give you a heads up. I tried running sproc from Google Doc but got error message and had to add another END at end of the code.
Then when I tried running sproc as:
EXEC CleanupSchema ‘Stage’, ‘w’
got error message stating @dropSchema parameter missing so I had to add integer as 3rd parameter
Once this was done it worked perfectly so thanks for all your work.
8 years later and still useful. Many thanks for this.
Good to know. Thanks, Rob.
2018 and still useful,
I’ve got some scalar values that I need to manually remove but this works great
🙂
Good to know David. Thanks for the comment.
We use this script but when it runs concurrently (on more than one schema), we do get deadlocks. It involves “sys.sysschobjs” and “sys.syssingleobjrefs”.
Here’s a screenshot from SQL Server Profiler: https://i.imgur.com/e38My8o.png
Any idea on how to improve this script for concurrency access?
Really useful script ! thanks mate .
This is a great script, thank you for posting.
Very useful. Thanks
Hi Ranjith,
With your permission I would like to use this script in our project for cleanup activity. Let me know if it is ok to do so.
Sure. Please go ahead. Thanks for dropping a note
Thanks Ranjith! Works smooth and easy.
Thanks Ivar
Thanks so much for this extremely useful script! I used this to drop hundreds of objects created by Redgate’s SQLCop and SQL test applications. Unfortunately, their object names included spaces so this script didn’t work right out of the gate. But, with the quick modification listed below I was able to wrap the object names in brackets and it worked like a dream!
..SCHEMA_NAME(SO.schema_id)+”.”+”[” + SO.[Name] +”]”
Thanks Jason
Thank you for very helpful script to drop schema and dependent objects. It saved lots of time doing manual deletion of objects.
Just want to add that User defined types are not deleted via this script.