Drop a SQL Server Schema and all related Objects

I love to organize my SQL Server database objects using schemas and in my current project I need to create a lot of them dynamically so I also need to manage a way to delete them :).

So I implemented a stored procedure that allows me to delete the schema and all it related objects in a database (also specified by parameter).

Usage:

exec dbo.DropSchema @schemaName = ‘schema’, @database = ‘[Database]’, @whatif = 1

You can download the procedure script here.

If you find any bugs or have any sugestion please fell free to comment.

Resources:

Advertisements

8 thoughts on “Drop a SQL Server Schema and all related Objects

  1. Edwin Stoteler says:

    Thx allot, this helped me allot. Even thought this post is already 2 years old
    You only forgot to remove user-defined data types. And because of that the drop of the my schema failed. Also added some transactions so everything is rolledback when it fails.

  2. Anonymous says:

    On a case sensitive server the source complains about a couple of references to SYS.OBJECTS.
    The projection list references three function types that are not included in the PREDICATE clause ”IF”,”FS”,”FT” adding these to the list: AND SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”) solves the issue.

  3. Anonymous says:

    Thank you. Very useful code!
    In my case I have a lot of sequences so I changed the code to drop them to:
    — Drop remaining objects

    SELECT @dropSqlCommand = @dropSqlCommand
    + 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=”SO” THEN ”DROP SEQUENCE [”+SCHEMA_NAME(SO.schema_id)+”].[”+ SO.[Name] + ”]”
    WHEN SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”) THEN ”DROP FUNCTION [”+SCHEMA_NAME(SO.schema_id)+”].[”+ SO.[Name] + ”]”
    END
    + CHAR(13)
    FROM sys.objects SO
    WHERE SO.schema_id = schema_id(@schemaName)
    AND SO.type IN (”PK”, ”FN”, ”TF”, ”TR”, ”V”, ”U”, ”P”, ”SO”)
    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

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