Drop Database Objects Practically Risk-Free!

FIRST OF A TWO PART SERIES

Some months ago, a fellow DBA came to me and expressed her concern over the normal housecleaning process that occurred at her company.  Developers or product owners would submit changes for objects which were no longer used, only to find that sometimes, they were.  No damage had been done, but the possibility of creating an emergent situation was there.

I could well understand her concern.  Most of us who have been DBAs for any length of time have probably come across a similar scenario.  I thought it would be fun to write a process that reduced the risk of dropping database objects and made rollbacks a snap.

My process works in two parts:  the first is to rename the object; the second is to drop it after 120 days (unless an extension of that time is requested).  The extension works both ways – it can drop an object before 120 days if needed, or after if requested. We will cover the rename portion of the process today.

This process works with views, tables, functions, stored procedures and synonyms.  To make it as safe as possible, a number of safeguards were built in:

  • First, a table was put on a database to store any rollback information and record extension request dates, in case extra time was needed before the object was dropped. A ‘todrop’ schema is also created to store the newly renamed objects.
  • Next, a sanity check is performed.  There is no point in faulty data being sent to the recording table because of a misspelling, incorrect schema (or incorrect database).
  • There is an optional parameter that checks for things that can break if the object is dropped.  If you activate this parameter, and something would be harmed in the dropping of this object, you will be unceremoniously disconnected.  But not before I tell you why in the results.
  • A check is performed to see if we are dealing with a table.  If so, we have to look for FK constraints.  If any are found, they are scripted out to the recording table for rollback purposes.  If you are using FKs with UPDATE or DELETE CASCADE, you will once again be unceremoniously dumped. A message asking you why might be in the cards for a future update.
  • If you manage to pass all this, you’re good to go.  Your object will be renamed with _DATEHERE_objectname and transferred to the todrop schema.  You’ll find the information posted in your table.

Let’s see this at work in the AdventureWorks2012 database:

--First, we'll need a sandbox database and a table to store information on renamed objects and FK constraints...
USE [master];
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'Sandbox')
    CREATE DATABASE Sandbox;
GO

USE [Sandbox];
GO

CREATE TABLE [dbo].[DropObject]
(
    [DatabaseName] [sysname] NULL,
    [SchemaName] [NVARCHAR](50) NULL,
    [ObjectName] [NVARCHAR](200) NULL,
    [ChangeNumber] [NVARCHAR](20) NULL,
    [ChangeSubmitter] [NVARCHAR](100) NULL,
    [ChangeSubmitterEmail] [NVARCHAR](200) NULL,
    [ModifyDate] [DATE] NULL,
    [ConstraintScript] [NVARCHAR](MAX) NULL,
    [ExceptionDate] [DATE] NULL
) ON [PRIMARY];
GO

ALTER TABLE [dbo].[DropObject]
ADD CONSTRAINT [DF_DropObject_ModifyDate]
    DEFAULT (GETDATE()) FOR [ModifyDate];
GO

USE [AdventureWorks2012];
GO

IF OBJECT_ID('tempdb..#FK') IS NOT NULL
    DROP TABLE #FK;

IF OBJECT_ID('tempdb..#DropScript') IS NOT NULL
    DROP TABLE #DropScript;

IF OBJECT_ID('tempdb..#ConstraintScript') IS NOT NULL
    DROP TABLE #ConstraintScript;



IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = 'todrop')
BEGIN
    EXEC ('CREATE SCHEMA [todrop] AUTHORIZATION dbo;');
END;

CREATE TABLE #ConstraintScript
(
    FKConstraintScript NVARCHAR(MAX)
);

DECLARE @schema_name sysname;
DECLARE @table_name sysname;
DECLARE @constraint_name sysname;
DECLARE @constraint_object_id INT;
DECLARE @referenced_object_name sysname;
DECLARE @is_disabled BIT;
DECLARE @is_not_for_replication BIT;
DECLARE @is_not_trusted BIT;
DECLARE @delete_referential_action TINYINT;
DECLARE @update_referential_action TINYINT;
DECLARE @tsql NVARCHAR(4000);
DECLARE @tsql2 NVARCHAR(4000);
DECLARE @fkCol sysname;
DECLARE @pkCol sysname;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);
DECLARE @referenced_schema_name sysname;


DECLARE @modifydate DATE = CAST(GETDATE() AS DATE);
DECLARE @ConstraintScript NVARCHAR(MAX);
DECLARE @drop NVARCHAR(MAX) = N'';


--Input Parameters

DECLARE @FindObjectsThatCouldBreak BIT = '0'; --we use this to find dependencies that could keep a table rename or drop from happening
DECLARE @DatabaseName sysname = 'AdventureWorks2012';
DECLARE @SchemaName NVARCHAR(50) = N'dbo';
DECLARE @ObjectName NVARCHAR(200) = N'ErrorLog';
DECLARE @ChangeNumber NVARCHAR(20) = N'ABC1234567891';
DECLARE @ChangeSubmitter NVARCHAR(100) = N'John Smith';
DECLARE @ChangeSubmitterEmail NVARCHAR(200) = N'jsmith1@adventureworks.com';

--End Input Parameters

DECLARE @InputString NVARCHAR(MAX) =
        (
            SELECT QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
        );
DECLARE @RenamedObject NVARCHAR(MAX) =
        (
            SELECT '_' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR) + '_' + @ObjectName
        );

--Let's do a quick sanity check before we get started to make sure we have a viable object.

SELECT 'The object ' + s.name + '.' + o.name + ' exists in this database.' AS 'SanityCheckPass'
FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON o.schema_id = s.schema_id
WHERE is_ms_shipped = 0
      AND s.name = @SchemaName
      AND o.name = @ObjectName;

IF @@ROWCOUNT = 0
    THROW 51000, 'Either the schema name or the object name is incorrect.  Please verify and try again.', 1;



/* ... Time to find what else could break if we drop this guy.  This could be run separately with @InputString hardcoded if you prefer.*/

IF @FindObjectsThatCouldBreak = 1
BEGIN

    SELECT referencing_schema_name,
           referencing_entity_name AS ReferencingObjectThatCouldBreak,
           referencing_class_desc,
           is_caller_dependent
    FROM sys.dm_sql_referencing_entities(@InputString, 'OBJECT');



    IF @@ROWCOUNT > 0
        THROW 51000, 'Dropping this object could cause other objects to break!  See previous query result in the Results tab.', 1;

END;

/* Now, let's see if we are dealing with any tables. If we are, a couple of extra steps are necessary to ensure that it is safe
(or even possible) to drop the table. */

SELECT DB_NAME() AS DatabaseName,
       s.name AS SchemaName,
       o.name AS ObjectName
INTO #FK
FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON o.schema_id = s.schema_id
WHERE is_ms_shipped = 0
      AND type_desc = 'USER_TABLE'
      AND o.name = @ObjectName;



CREATE TABLE #DropScript
(
    drop_script NVARCHAR(MAX)
);



/* Next, let's make sure that we don't have any FK constraints with cascading involved.  If we do, the process needs to stop until it has 
a chance to be evaluated carefully, so that nothing unexpected breaks in other processes. We'll also show any FK columns that are going out to 
other tables for an FYI, as these are potential breaking points.*/
IF
(
    SELECT COUNT(*) FROM #FK
) >= 1
BEGIN

    DECLARE @FKObjectName sysname =
            (
                SELECT ObjectName FROM #FK
            );
    DECLARE @FKSchemaName sysname =
            (
                SELECT SchemaName FROM #FK
            );


    DECLARE @t TABLE
    (
        PKTABLE_QUALIFIER sysname NULL,
        PKTABLE_OWNER sysname NULL,
        PKTABLE_NAME sysname NULL,
        PKCOLUMN_NAME sysname NULL,
        FKTABLE_QUALIFIER sysname NULL,
        FKTABLE_OWNER sysname NULL,
        FKTABLE_NAME sysname NULL,
        FKCOLUMN_NAME sysname NULL,
        KEY_SEQ SMALLINT NULL,
        UPDATE_RULE SMALLINT NULL,
        DELETE_RULE SMALLINT NULL,
        FK_NAME sysname NULL,
        PK_NAME sysname NULL,
        DEFERRABILITY SMALLINT NULL
    );

    INSERT INTO @t
    EXEC sp_fkeys @pktable_name = @FKObjectName,
                  @pktable_owner = @FKSchemaName;



    /*Let's also check that this table doesn't have columns that are foreign keys to other tables. If so, we need to know now, or we may 
  not be able to drop the table later.*/

    SELECT PKTABLE_OWNER + '.' + PKTABLE_NAME + '.' + PKCOLUMN_NAME AS PK,
           FKTABLE_OWNER + '.' + FKTABLE_NAME AS FKTable,
           FKCOLUMN_NAME AS FKColumn_MustBeHandledPriorToTableDrop,
           CASE
               WHEN DELETE_RULE = 1 THEN
                   'No Action'
               WHEN DELETE_RULE = 0 THEN
                   'Cascade'
               ELSE
                   'Unknown'
           END AS DELETE_RULE,
           CASE
               WHEN UPDATE_RULE = 1 THEN
                   'No Action'
               WHEN UPDATE_RULE = 0 THEN
                   'Cascade'
               ELSE
                   'Unknown'
           END AS UPDATE_RULE,
           KEY_SEQ
    FROM @t
    ORDER BY FKTABLE_OWNER,
             FKTABLE_NAME,
             FKCOLUMN_NAME,
             KEY_SEQ;

    IF
    (
        SELECT COUNT(*) FROM @t WHERE UPDATE_RULE = '0'
    ) >= 1
    OR
    (
        SELECT COUNT(*) FROM @t WHERE DELETE_RULE = '0'
    ) >= 1
    BEGIN

        DECLARE @StopNOWmsg NVARCHAR(MAX);
        SET @StopNOWmsg
            = N'This table has FKs to other tables with either ON UPDATE or ON DELETE CASCADE.  Please refer to query results.  You''ll want to investigate further before dropping it, otherwise, other processes could unexpectedly break.';
        THROW 51000, @StopNOWmsg, 1;

    END;


    --Send constraint script over to sandbox database for storage/rollback purposes.  Largely derived from https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx

    DECLARE FKcursor CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME(parent_object_id),
           OBJECT_NAME(parent_object_id),
           name,
           OBJECT_NAME(referenced_object_id),
           object_id,
           is_disabled,
           is_not_for_replication,
           is_not_trusted,
           delete_referential_action,
           update_referential_action,
           OBJECT_SCHEMA_NAME(referenced_object_id)
    FROM sys.foreign_keys
    WHERE OBJECT_SCHEMA_NAME(parent_object_id) = @SchemaName
          AND OBJECT_NAME(parent_object_id) = @ObjectName
    ORDER BY OBJECT_SCHEMA_NAME(parent_object_id),
             OBJECT_NAME(parent_object_id);

    OPEN FKcursor;

    FETCH NEXT FROM FKcursor
    INTO @schema_name,
         @table_name,
         @constraint_name,
         @referenced_object_name,
         @constraint_object_id,
         @is_disabled,
         @is_not_for_replication,
         @is_not_trusted,
         @delete_referential_action,
         @update_referential_action,
         @referenced_schema_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN



        IF @action <> 'CREATE'
            SET @tsql
                = N'ALTER TABLE ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) + N' DROP CONSTRAINT '
                  + QUOTENAME(@constraint_name) + N';';

        ELSE
        BEGIN

            SET @tsql
                = N'ALTER TABLE ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) + CASE @is_not_trusted
                                                                                                  WHEN 0 THEN
                                                                                                      ' WITH CHECK '
                                                                                                  ELSE
                                                                                                      ' WITH NOCHECK '
                                                                                              END + N' ADD CONSTRAINT '
                  + QUOTENAME(@constraint_name) + N' FOREIGN KEY (';

            SET @tsql2 = N'';

            DECLARE ColumnCursor CURSOR FOR
            SELECT COL_NAME(fk.parent_object_id, fkc.parent_column_id),
                   COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
            FROM sys.foreign_keys fk
                INNER JOIN sys.foreign_key_columns fkc
                    ON fk.object_id = fkc.constraint_object_id
            WHERE fkc.constraint_object_id = @constraint_object_id
            ORDER BY fkc.constraint_column_id;

            OPEN ColumnCursor;

            SET @col1 = 1;

            FETCH NEXT FROM ColumnCursor
            INTO @fkCol,
                 @pkCol;

            WHILE @@FETCH_STATUS = 0
            BEGIN

                IF (@col1 = 1)
                    SET @col1 = 0;

                ELSE
                BEGIN

                    SET @tsql = @tsql + N',';

                    SET @tsql2 = @tsql2 + N',';

                END;

                SET @tsql = @tsql + QUOTENAME(@fkCol);

                SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);

                FETCH NEXT FROM ColumnCursor
                INTO @fkCol,
                     @pkCol;

            END;

            CLOSE ColumnCursor;

            DEALLOCATE ColumnCursor;

            SET @tsql
                = @tsql + N' ) REFERENCES ' + QUOTENAME(@referenced_schema_name) + N'.'
                  + QUOTENAME(@referenced_object_name) + N' (' + @tsql2 + N')';

            SET @tsql = @tsql + N' ON UPDATE ' + CASE @update_referential_action
                                                     WHEN 0 THEN
                                                         'NO ACTION '
                                                     WHEN 1 THEN
                                                         'CASCADE '
                                                     WHEN 2 THEN
                                                         'SET NULL '
                                                     ELSE
                                                         'SET DEFAULT '
                                                 END + N' ON DELETE ' + CASE @delete_referential_action
                                                                            WHEN 0 THEN
                                                                                'NO ACTION '
                                                                            WHEN 1 THEN
                                                                                'CASCADE '
                                                                            WHEN 2 THEN
                                                                                'SET NULL '
                                                                            ELSE
                                                                                'SET DEFAULT '
                                                                        END + CASE @is_not_for_replication
                                                                                  WHEN 1 THEN
                                                                                      ' NOT FOR REPLICATION '
                                                                                  ELSE
                                                                                      ''
                                                                              END + N';';

        END;

        INSERT INTO #ConstraintScript
        (
            FKConstraintScript
        )
        SELECT @tsql;

        IF @action = 'CREATE'
        BEGIN

            SET @tsql
                = N'ALTER TABLE ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) + CASE @is_disabled
                                                                                                  WHEN 0 THEN
                                                                                                      ' CHECK '
                                                                                                  ELSE
                                                                                                      ' NOCHECK '
                                                                                              END + N'CONSTRAINT '
                  + QUOTENAME(@constraint_name) + N';';

            PRINT @tsql;

        END;

        FETCH NEXT FROM FKcursor
        INTO @schema_name,
             @table_name,
             @constraint_name,
             @referenced_object_name,
             @constraint_object_id,
             @is_disabled,
             @is_not_for_replication,
             @is_not_trusted,
             @delete_referential_action,
             @update_referential_action,
             @referenced_schema_name;

    END;

    CLOSE FKcursor;

    DEALLOCATE FKcursor;

    IF
    (
        SELECT COUNT(*) FROM #ConstraintScript
    ) >= 1
    BEGIN
        SELECT *
        FROM #ConstraintScript;
        SELECT 'Rollback script for foreign key constraint successfully created for import to Sandbox...' AS FKRollbackConfirmation;

    END;

    --If the coast is clear, we'll go ahead and drop the FK constraints....

    SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + N'.' + QUOTENAME(ct.name) + N' DROP CONSTRAINT ' + QUOTENAME(fk.name) + N';'
    FROM sys.foreign_keys AS fk
        INNER JOIN sys.tables AS ct
            ON fk.parent_object_id = ct.[object_id]
        INNER JOIN sys.schemas AS cs
            ON ct.[schema_id] = cs.[schema_id]
    WHERE cs.name = @SchemaName
          AND ct.name = @ObjectName;

    INSERT #DropScript
    (
        drop_script
    )
    SELECT @drop;

    SELECT @drop AS 'DropScriptCreated';
    EXECUTE sp_executesql @drop;

END;



----Now, we'll rename the object and transfer it to the 'todrop' schema for safekeeping.

EXEC sp_rename @InputString, @RenamedObject;

IF @@ERROR = 0
BEGIN

    SELECT 'Successfully renamed ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ''' to '''
           + QUOTENAME(@SchemaName) + '.[' + @RenamedObject + '].' AS ObjectRenameOutput;

END;


DECLARE @TransferObject NVARCHAR(MAX) =
        (
            SELECT '[' + @SchemaName + ']' + '.' + '[' + @RenamedObject + ']'
        );



DECLARE @sql NVARCHAR(MAX) = N'ALTER SCHEMA todrop TRANSFER ' + @TransferObject + N';';
PRINT @sql;
EXECUTE sp_executesql @sql;

IF @@ERROR = 0
BEGIN
    DECLARE @SchemaConfimSQL NVARCHAR(MAX)
        = N'Successfully transferred [' + @SchemaName + N'].[' + @ObjectName + N'] to ''todrop'' schema.';
    SELECT @SchemaConfimSQL AS SchemaConfirmation;
END;

--Finally, we'll send all the information over to the sandbox for reference/safekeeping, and we're done!

IF
(
    SELECT COUNT(*) FROM #ConstraintScript
) >= 1
BEGIN

    DECLARE @FinalConstraintScript NVARCHAR(MAX);
    DECLARE @InsertSandboxCursor CURSOR;
    DECLARE @InsertSQL NVARCHAR(MAX);

    SET @InsertSandboxCursor = CURSOR FAST_FORWARD LOCAL FOR
    SELECT FKConstraintScript
    FROM #ConstraintScript;

    OPEN @InsertSandboxCursor;

    FETCH NEXT FROM @InsertSandboxCursor
    INTO @FinalConstraintScript;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        SET @InsertSQL
            = N'INSERT INTO Sandbox.dbo.DropObject SELECT ''' + @DatabaseName + N''',''' + @SchemaName + N''','''
              + @ObjectName + N''',''' + @ChangeNumber + N''',''' + @ChangeSubmitter + N''',''' + @ChangeSubmitterEmail
              + N''',''' + CAST(@modifydate AS NVARCHAR) + N''',''' + @FinalConstraintScript + N''', NULL';



        PRINT @InsertSQL;
        EXECUTE sp_executesql @InsertSQL;

        FETCH NEXT FROM @InsertSandboxCursor
        INTO @FinalConstraintScript;
    END;

    CLOSE @InsertSandboxCursor;
    DEALLOCATE @InsertSandboxCursor;

END;

IF
(
    SELECT COUNT(*) FROM #ConstraintScript
) = 0
BEGIN

    INSERT INTO Sandbox.dbo.DropObject
    (
        DatabaseName,
        SchemaName,
        ObjectName,
        ChangeNumber,
        ChangeSubmitter,
        ChangeSubmitterEmail,
        ModifyDate,
        ConstraintScript
    )
    SELECT @DatabaseName,
           @SchemaName,
           @ObjectName,
           @ChangeNumber,
           @ChangeSubmitter,
           @ChangeSubmitterEmail,
           @modifydate,
           NULL;

    SELECT 'Information on ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)
           + ' was successfully transferred to Sandbox.' AS SandboxTransferConfirmation;

END;

--Now, go and check Sandbox:

SELECT *
FROM Sandbox.dbo.DropObject;

/* And clean up our mess */

DROP TABLE #FK;
DROP TABLE #DropScript;
DROP TABLE #ConstraintScript;



DROP DATABASE Sandbox;

If you decide to use this for real, keep your Sandbox database with the table. Here are screenshots of the output:

A standard rename with no FK constraints

Rename with one FK constraint (yes, it will do multiple constraints)
Uh oh….the dreaded FK with ON UPDATE or DELETE CASCADE!
At least it shows you the issue!

My friend was happy with her early Christmas present.  I was happy to help.  The next post will cover the drop process.

One comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s