EX0021 : Check DROP TABLE statements in order to avoid unintentional data loss

Introduction

Potential data loss may occure from executing undesired DROP TABLE statements.

Description

Executing DROP TABLE statements without appropriate review and precautions can lead to the irreversible loss of valuable data, impacting both production and development environments.

A common reason for executing unwanted DROP TABLE statements is when unintentionally running a database create script that conditionally drops existing database objects before recreating them.

Example of a DROP TABLE statement execured in case the target table exists

IF OBJECT_ID(N'[dbo].[MyTable0]', N'U') IS NOT NULL
DROP TABLE [dbo].[MyTable0];

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable0]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable0]
GO

Executing the above command will permanently delete the entire Employees table and all its data. Without a prior backup or version control, this action cannot be undone, which could lead to significant data loss.

Irreversible data loss when executed without prior backup or recovery plans.
Potential impact on application functionality that depends on the deleted table.

How to fix

Ensure data safety by implementing precautions before executing DROP TABLE statements in T-SQL.

Follow these steps to address the issue:

1.Identify the tables scheduled for deletion and assess their usage in the current database environment. Confirm that removal will not disrupt application functionality.

2.Make a backup of the table and related data. Use the SELECT INTO command to create a copy of the data or perform a database backup using SQL Server Management Studio (SSMS).

3.Review transactional dependencies and verify that no stored procedures, triggers, or views are reliant on the table. Make necessary adjustments to avoid errors post-deletion.

4.Add a rule IGNORE:EX0021 comment next the DROP TABLE statement in order to suppress the rule violation and allow the DROP TABLE statement.

5.Execute the DROP TABLE statement only after validating the above checks and ensuring you have the backup securely stored.

For example:

-- Back up the current data
SELECT * INTO [dbo].[MyTableBackup] FROM dbo.MyTable0;

-- Safely drop the table after ensuring backups are in place
DROP TABLE [dbo].[MyTable0];

IF OBJECT_ID(N'[dbo].[MyTable0]', N'U') IS NOT NULL
DROP TABLE [dbo].[MyTable0]; -- IGNORE:EX0021, the drop table is reviewed and considered safe.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

DROP TABLE Test.Greeting

DROP TABLE Test.Greeting -- IGNORE:EX0021

DROP TABLE #temp, aaa

Analysis Results

	Message	Line	Column
1	EX0021 : Check DROP TABLE statements in order to avoid unintentional data loss.	1	0
2	EX0021 : Check DROP TABLE statements in order to avoid unintentional data loss.	5	0

Analysis Rules