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;
Here are screenshots of the output:
My friend was happy with her early Christmas present. I was happy to help. The next post will cover the drop process.