Dan Rigsby - Coding Up Style

.Net, C#, & Wcf Development

« Preserving line breaks in xml while transforming to html with xslt
Enterprise Library 4.0 Product Backlog Available »

How to Drop All Database Objects Beginning with a Prefix

Posted by Dan Rigsby on January 8th, 2008

We prefix all of our database objects with a namespace scope.  Objects begin with "ININ_" followed by an application prefix.  So all database objects relating to to an application called "ABC" will begin with the prefix "ININ_ABC_".  This namespacing really helps out when multiple applications share the same database.

Advantages of this approach include:

  1. Identifying what application an object belongs to
  2. Finding all objects for an application in the sysobject tables

But, this post isn’t about how to name database objects…

To test our database scripts, we normally drop a database and recreate through our build process. However, when multiple applications share the same database, we need to only drop the database objects relating to that application. 

The stored procedure below takes in an application prefix and drops all database objects whose name starts with that prefix.  This script will work for any prefix you might use to segregate your objects. 

This was written for SQL Server 2005, but it could be easily modified to work with older versions.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'DropApplication') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[DropApplication]
GO

CREATE PROCEDURE [dbo].[DropApplication]
(
    @ApplicationPrefix varchar(64)
)
/************************************************************/
-- DropApplication
-- 
-- Date Created: 8/19/2007
-- Created By:     Dan Rigsby (www.danrigsby.com)
-- 
-- Purpose:         Deletes all of the sysobjects in the database
--                 for an application based on sysobject name prefix
--
-- Example:         To delete all sysobjects for an application called
--                 ISupport whose object are prefixed with 'ININ_ABC' call: 
--                 EXEC DropApplication 'ININ_ABC'
/************************************************************/

AS
BEGIN
    DECLARE @Statement AS varchar(4000)
    DECLARE @ParentID AS int
    DECLARE @ReferenceID AS int
    DECLARE @ParentName AS varchar(256)
    DECLARE @ObjectName AS varchar(256)
    DECLARE @ObjectType AS varchar(5)

    -- Step 1: Delete procedures
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'P', N'PC', N'X', N'RF')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP PROCEDURE ' + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 2: Delete functions
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'IF', N'FN', N'TF')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP FUNCTION ' + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 3: Delete views
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'V')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP VIEW ' + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 4: Delete FKs
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id], [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'U')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name]
            FROM sysobjects
            WHERE [parent_obj] = @ParentID AND [type] IN (N'F')
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName
        WHILE (@@fetch_status = 0)
        BEGIN
            EXEC (' ALTER TABLE ' + @ParentName +
                ' DROP CONSTRAINT ' + @ObjectName)

            FETCH NEXT FROM cr2 INTO @ObjectName
        END
        CLOSE cr2
        DEALLOCATE cr2

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 5: Delete any FK constraints in other tables that reference our tables
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'U')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ReferenceID
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name], [type], [parent_object_id]
            FROM sys.foreign_keys
            WHERE [referenced_object_id] = @ReferenceID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName, @ObjectType, @ParentID
        WHILE (@@fetch_status = 0)
        BEGIN
            -- Get parent name
            SELECT @ParentName = [name]
            FROM sysobjects
            WHERE [id] = @ParentID

            SELECT @Statement =
                CASE (@ObjectType)
                    WHEN 'F' THEN
                        ' ALTER TABLE ' + @ParentName +
                        ' DROP CONSTRAINT ' + @ObjectName
                END
            EXEC (@Statement)

            FETCH NEXT FROM cr2 INTO @ObjectName, @ObjectType, @ParentID
        END
        CLOSE cr2
        DEALLOCATE cr2

        FETCH NEXT FROM cr1 INTO @ReferenceID
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 6: Delete tables, triggers, FKs, and constraints
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id], [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'U')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name], [type]
            FROM sysobjects
            WHERE [parent_obj] = @ParentID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName, @ObjectType
        WHILE (@@fetch_status = 0)
        BEGIN
            SELECT @Statement =
                CASE (@ObjectType)
                    WHEN 'TR' THEN
                        ' DROP TRIGGER ' + @ObjectName
                    ELSE
                        ' ALTER TABLE ' + @ParentName +
                        ' DROP CONSTRAINT ' + @ObjectName
                END
            EXEC (@Statement)

            FETCH NEXT FROM cr2 INTO @ObjectName, @ObjectType
        END
        CLOSE cr2
        DEALLOCATE cr2

        EXEC (' DROP TABLE ' + @ParentName)

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 7: Drop users
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sys.database_principals
        WHERE [name] LIKE @ApplicationPrefix + '%'
            AND [type] IN (N'S')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ObjectName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP USER ' + @ObjectName)
        FETCH NEXT FROM cr1 INTO @ObjectName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 8: Drop roles
    DECLARE cr1 CURSOR FOR
    (
        SELECT [principal_id], [name]
        FROM sys.database_principals
        WHERE [name] LIKE @ApplicationPrefix + '%'
            AND [type] in (N'R')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name]
            FROM sys.database_role_members DRM
                INNER JOIN sys.database_principals DP
                    ON DRM.[member_principal_id] = DP.[principal_id]
            WHERE [role_principal_id] = @ParentID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName
        WHILE (@@fetch_status = 0)
        BEGIN
            EXEC
            ('
                EXEC dbo.sp_droprolemember ''' + @ParentName + ''', ''' + @ObjectName + '''
            ')
            FETCH NEXT FROM cr2 INTO @ObjectName
        END
        CLOSE cr2
        DEALLOCATE cr2

        EXEC ('DROP ROLE ' + @ParentName)

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1
END
GO

Database objects are dropped in the following order:

  1. Procedures
  2. Functions
  3. Views
  4. Foreign Keys
  5. Foreign Key constraints in other tables
  6. Triggers and Constraints
  7. Tables
  8. Users
  9. Roles

This entry was posted on Tuesday, January 8th, 2008 at 9:06 pm and is filed under Database, MSSQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

« Preserving line breaks in xml while transforming to html with xslt
Enterprise Library 4.0 Product Backlog Available »