Drop It Like It’s Hot (Part 2 of the Rename/Drop Objects Process)

In my previous article, I showed my process for renaming objects and storing their information in a table for recording and rollback purposes.  Today, I’ll demonstrate the drop process.

First of all, I’m going to rename some objects. Let’s look at our Sandbox.dbo.DropObjects table to see what it looks like so far:

Let’s create our stored procedure to drop the objects 120 days after renaming (you can make the number of days anything you want):

USE [Sandbox]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DropRenamedObjects]

AS
BEGIN

    SET NOCOUNT ON;

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

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

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

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

    CREATE TABLE #Results
    (
        DatabaseName sysname NULL,
        SchemaName sysname NULL,
        ObjectName sysname NULL,
        ObjectType sysname NULL,
        modify_date DATE NULL
    );

    CREATE TABLE #FinalScriptTable
    (
        Script NVARCHAR(MAX) NULL
    );

    DECLARE @dbName sysname;
    DECLARE @dbCursor CURSOR;
    DECLARE @sql NVARCHAR(MAX);


    CREATE TABLE #DropConfirmationSuccessful
    (
        DatabaseName sysname NULL,
        SchemaName sysname NULL,
        ObjectName sysname NULL,
        ObjectType sysname NULL,
        modify_date DATE NULL
    );

    CREATE TABLE #DropObject
    (
        DatabaseName sysname NULL,
        SchemaName sysname NULL,
        ObjectName sysname NULL,
        ModifyDate DATE NULL,
        ExceptionDate DATE NULL
    );


    --First, let's go throughout the instance and collect any objects eligible to be dropped.

    SET @dbCursor = CURSOR FAST_FORWARD LOCAL FOR
    SELECT name
    FROM sys.databases
    WHERE source_database_id IS NULL
          AND is_read_only = 0
          AND state_desc = 'ONLINE'
    ORDER BY name;

    OPEN @dbCursor;

    FETCH NEXT FROM @dbCursor
    INTO @dbName;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql
            = N'USE [' + @dbName + N'];
	INSERT INTO #Results
	SELECT '''     + @dbName
              + N''' AS DatabaseName,
	s.name AS SchemaName,
	o.name AS ObjectName, 
	CASE WHEN o.type = ''U'' THEN ''TABLE''
	     WHEN o.type = ''FN'' THEN ''FUNCTION''
		 WHEN o.type = ''IF'' THEN ''FUNCTION''
		 WHEN o.type = ''TF'' THEN ''FUNCTION''
		 WHEN o.type = ''V'' THEN ''VIEW''
		 WHEN o.type = ''P'' THEN ''PROCEDURE''
		 WHEN o.type = ''SN'' THEN ''SYNONYM''
	END AS ObjectType,
	CAST(modify_date AS DATE) AS ModifyDate
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 = ''todrop''
AND type IN (''U'', ''FN'', ''IF'', ''TF'', ''V'', ''P'', ''SN'')
AND o.name LIKE ''^_2%'' ESCAPE ''^''
ORDER BY DatabaseName, ObjectName; ';
        EXECUTE sp_executesql @sql;
        FETCH NEXT FROM @dbCursor
        INTO @dbName;
    END;

    CLOSE @dbCursor;
    DEALLOCATE @dbCursor;

    SELECT *
    FROM #Results;

    --Get distinct results from dbo.DropObject.  We do it this way because if a table has more than one FK constraint, there will be more than one entry into the table.

    INSERT INTO #DropObject
    (
        DatabaseName,
        SchemaName,
        ObjectName,
        ModifyDate,
        ExceptionDate
    )
    SELECT DISTINCT
           DatabaseName,
           SchemaName,
           ObjectName,
           ModifyDate,
           ExceptionDate
    FROM Sandbox.dbo.DropObject;

    SELECT *
    FROM #DropObject;
    --If we find anything, we'll make the script for it...

    IF
    (
        SELECT COUNT(*) FROM #Results
    ) >= 1
    BEGIN
        INSERT INTO #FinalScriptTable
        (
            Script
        )
        SELECT 'USE ' + r.DatabaseName + '; DROP ' + r.ObjectType + '  [' + r.SchemaName + '].[' + r.ObjectName + '];'
        FROM #Results AS r
            INNER JOIN #DropObject AS do
                ON do.DatabaseName = r.DatabaseName
                   AND do.ObjectName = REPLACE(r.ObjectName, LEFT(r.ObjectName, 12), '')
        WHERE (
                  r.modify_date <= DATEADD(DAY, -120, GETDATE())
                  AND do.ExceptionDate IS NULL
              )
              OR (do.ExceptionDate <= GETDATE());
        --Show objects that are about to be dropped
        SELECT 'Object to be dropped',
               r.*
        FROM #Results AS r
            INNER JOIN #DropObject AS do
                ON do.DatabaseName = r.DatabaseName
                   AND do.ObjectName = REPLACE(r.ObjectName, LEFT(r.ObjectName, 12), '')
        WHERE (
                  r.modify_date <= DATEADD(DAY, -120, GETDATE())
                  AND do.ExceptionDate IS NULL
              )
              OR (do.ExceptionDate <= GETDATE());



        --Let the end user know that we're trying to drop the objects...

        DECLARE @DropScriptName sysname;
        DECLARE @dropCursor CURSOR;

        SET @dropCursor = CURSOR FAST_FORWARD LOCAL FOR
        SELECT Script
        FROM #FinalScriptTable;

        OPEN @dropCursor;

        FETCH NEXT FROM @dropCursor
        INTO @DropScriptName;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXEC sp_executesql @DropScriptName;
            SELECT 'Trying to execute ' + @DropScriptName AS DropObjectUpdate;
            FETCH NEXT FROM @dropCursor
            INTO @DropScriptName;
        END;

        CLOSE @dropCursor;
        DEALLOCATE @dropCursor;


        --Let's check and see if everything worked, and record if it didn't

        DECLARE @dbDropName sysname;
        DECLARE @dbDropSuccessfulCursor CURSOR;
        DECLARE @DropConfirmationsql NVARCHAR(MAX);

        SET @dbDropSuccessfulCursor = CURSOR FAST_FORWARD LOCAL FOR
        SELECT name
        FROM sys.databases
        WHERE source_database_id IS NULL
              AND is_read_only = 0
              AND state_desc = 'ONLINE'
        ORDER BY name;

        OPEN @dbDropSuccessfulCursor;

        FETCH NEXT FROM @dbDropSuccessfulCursor
        INTO @dbDropName;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @DropConfirmationsql
                = N'USE [' + @dbDropName + N'];
	INSERT INTO #DropConfirmationSuccessful
	SELECT '''                         + @dbDropName
                  + N''' AS DatabaseName,
	s.name AS SchemaName,
	o.name AS ObjectName, 
	CASE WHEN o.type = ''U'' THEN ''TABLE''
	     WHEN o.type = ''FN'' THEN ''FUNCTION''
		 WHEN o.type = ''IF'' THEN ''FUNCTION''
		 WHEN o.type = ''TF'' THEN ''FUNCTION''
		 WHEN o.type = ''V'' THEN ''VIEW''
		 WHEN o.type = ''P'' THEN ''PROCEDURE''
		 WHEN o.type = ''SN'' THEN ''SYNONYM''
	END AS ObjectType,
	CAST(modify_date AS DATE) AS ModifyDate
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 = ''todrop''
AND type IN (''U'', ''FN'', ''IF'', ''TF'', ''V'', ''P'', ''SN'')
AND o.name LIKE ''^_2%'' ESCAPE ''^''
ORDER BY DatabaseName, ObjectName; ';

            EXECUTE sp_executesql @DropConfirmationsql;
            FETCH NEXT FROM @dbDropSuccessfulCursor
            INTO @dbDropName;
        END;

        CLOSE @dbDropSuccessfulCursor;
        DEALLOCATE @dbDropSuccessfulCursor;



        --Let the end user know the end result!

        IF EXISTS (SELECT * FROM #DropConfirmationSuccessful)
            SELECT dcs.*,
                   'Drop object failed. Only objects listed here were unable to be dropped. See Messages tab for specifics.' AS FailureMessage
            FROM #DropConfirmationSuccessful AS dcs
                INNER JOIN #Results AS r
                    ON dcs.DatabaseName = r.DatabaseName
                       AND dcs.SchemaName = r.SchemaName
                       AND dcs.ObjectName = r.ObjectName
                LEFT OUTER JOIN Sandbox.dbo.DropObject AS do
                    ON do.DatabaseName = r.DatabaseName
                       AND do.ObjectName = REPLACE(r.ObjectName, LEFT(r.ObjectName, 12), '')
            WHERE (
                      r.modify_date <= DATEADD(DAY, -120, GETDATE())
                      AND do.ExceptionDate IS NULL
                  )
                  OR (do.ExceptionDate <= GETDATE());

        ELSE
        BEGIN

            SELECT 'Objects were successfully dropped.';

        END;





    END;

    DROP TABLE #Results;
    DROP TABLE #FinalScriptTable;
    DROP TABLE #DropConfirmationSuccessful;
    DROP TABLE #DropObject;


END;

Now, the objects in the table will be dropped after 120 days.  But what if you need them to be dropped before (or after)?  Both options work, but I’ll show you the before, and also what happens if there is a problem with the drop.

Let’s update a couple of exception dates:

The object todrop._2019-04-18_Address is referenced by a FK constraint, so we know that will be an issue with dropping the table.  We know this because we were warned during the rename process:

Here is the output:

So we see that the ErrorLog table was dropped, but the Person table was not, and why. If you were wondering what the table looks like when there are scripted constraints:

So there it is, my friend and fellow DBA’s early Christmas present.  This would be best put into a job that runs every day, but it could be run ad hoc as well. Hope you enjoy it.

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