Home > T-SQL > Script to drop all objects of a Schema in SQL Server

Script to drop all objects of a Schema in SQL Server

 

I have spent almost 3 hours to complete the script and test it for couple of sample scenarios during this weekend (31/01/10).  The script initially drops all the objects of the schema and then drops the schema itself. It 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.

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 SchemaName and the WorkTest. 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. Work for me.

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 🙂

Advertisements
  1. elias
    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.

  2. 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.

  3. Rich
    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.

  1. January 31, 2010 at 6:23 pm
  2. April 23, 2010 at 8:53 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: