Ranjith Kumar S's Blog

Script to drop all objects of a Schema in SQL Server

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

Ranjith

, , ,

69 responses to “Script to drop all objects of a Schema in SQL Server”

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

    Reply

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

      Reply

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

      Reply

  5. 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…

    Reply

      • 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)

        Reply

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

          Reply

  6. 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 🙂

    Reply

  10. 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!

    Reply

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

    Reply

    • 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 ?

      Reply

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

    Reply

  17. Cannot drop schema ‘hcprod’ because it is being referenced by object ‘External’.

    I am getting this error. What can be the cause?

    Reply

  20. 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?

    Reply

  21. What version of SQL server are you using? And do you have access to view the sys.objects on server?

    Reply

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

      Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

  36. Hi Ranjith, would you be so kind and attribute a specific license to your script release, e.g. MIT or BSD. Thanks.

    Reply

  39. 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!

    Reply

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

    Reply

  42. 2018 and still useful,
    I’ve got some scalar values that I need to manually remove but this works great

    🙂

    Reply

  43. 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?

    Reply

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

    Reply

  49. 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] +”]”

    Reply

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *