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 the objects of the schema and then drops the schema itself. And automatically takes care of all the object dependencies with in the schema by dropping all of them 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
These 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.
Hope it helps
- Ranjith
30 comments
April 2, 2010 at 11:57 pm
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
April 5, 2010 at 7:58 am
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.
June 3, 2010 at 12:40 pm
when selecting a function the test should be IN (‘FN’, ‘IF’, ‘TF’, ‘FS’, ‘FT’)
June 4, 2010 at 6:43 am
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.
August 18, 2010 at 1:13 am
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…
August 18, 2010 at 5:16 pm
Yes Rich. Currently it can not handle the spatial indexes and XML indexes. Thanks for commenting.
May 30, 2011 at 4:12 am
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)
May 31, 2011 at 5:03 pm
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.
August 12, 2011 at 6:10 am
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
August 14, 2011 at 6:28 am
Good to know that it is useful and saved some development time. And thanks for commenting.
September 24, 2011 at 10:40 am
Hi – very useful script
Thanks
November 3, 2011 at 6:06 pm
Very useful and live saving script..
Thanks,
Rod
November 4, 2011 at 8:24 am
Thanks Rod
November 3, 2011 at 7:31 pm
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!
November 4, 2011 at 8:26 am
Thanks Mark !!! Its good to hear that
December 13, 2011 at 3:00 am
Thanks Ranjith you’ve saved me a bunch of work, if only i’d come across it earlier !
Cheers, Mark.
January 4, 2012 at 10:04 pm
Excellent! Thx for sharing!
January 5, 2012 at 1:48 pm
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
January 30, 2012 at 5:48 am
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 ?
January 7, 2012 at 5:57 am
David, Thanks for commenting and I will add this fix to the doc
January 16, 2012 at 3:12 pm
Really useful. Thank you Ranjith!
April 7, 2012 at 9:24 am
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
April 19, 2012 at 6:55 am
Thanks Jude, I will add this change
April 16, 2012 at 10:49 am
Cannot drop schema ‘hcprod’ because it is being referenced by object ‘External’.
I am getting this error. What can be the cause?
July 20, 2012 at 4:08 pm
Thanks. big time saver. works great.
July 20, 2012 at 6:58 pm
Good to hear that
July 23, 2012 at 1:52 pm
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?
July 24, 2012 at 8:06 am
What version of SQL server are you using? And do you have access to view the sys.objects on server?
September 5, 2012 at 8:05 am
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.
October 4, 2012 at 8:05 pm
Thanks a lot!!! Very good!!!