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
Tags: Clean schema, Drop Schema, Drop Schema Objects, SQL Schema
[...] Today found this great post, here is a quick excerpt : Script to drop all objects in a Schema. January 31, 2010 Ranjith Leave a comment Go to comments. I wrote script to drop all the objects of a schema and finally drop the schema itself. It takes care of dropping the objects in order … Read the rest of this great post Here [...]
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.
Script to drop all objects of a Schema in SQL Server « ranjithk’s blog…
Kudos for a great Sql Server article – Trackback from SqlServerKudos…
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!