Script to drop all objects of a Schema in SQL Server
31 Sunday Jan 2010
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.
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