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.