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

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

1IF OBJECT_ID(N'[dbo].[MyTable0]', N'U') IS NOT NULL
2DROP TABLE [dbo].[MyTable0];
3
4IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable0]') AND type in (N'U'))
5DROP TABLE [dbo].[MyTable0]
6GO

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.

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:

1-- Back up the current data
2SELECT * INTO [dbo].[MyTableBackup] FROM dbo.MyTable0;
3
4-- Safely drop the table after ensuring backups are in place
5DROP TABLE [dbo].[MyTable0];
6
7IF OBJECT_ID(N'[dbo].[MyTable0]', N'U') IS NOT NULL
8DROP TABLE [dbo].[MyTable0]; -- IGNORE:EX0021, the drop table is reviewed and considered safe.

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Explicit Rules

There is no additional info for this rule.

Other Resources