Delete tables from a SQL Server database safely

Inherited database carrying dozens of obviously abandoned tables, backup copies from 2016, someone's tmp experiments, staging leftovers. I want them gone but carefully.

What is the safe procedure for a bulk table cleanup, dependencies included?

The command is one line, DROP TABLE name, so the craft is entirely in the safety around it. A procedure that has cleaned many inherited databases without a single restore:

1

Build the suspect list with evidence: join sys.tables to sys.dm_db_index_usage_stats and let last_user_seek, scan and update per table testify. The usage DMV resets at service restart, so note the uptime, a month of uptime with zero reads on a table is strong evidence, two days of uptime proves nothing yet.

2

Check what would break: sys.foreign_keys where the suspect is the referenced table reveals children pointing at it, and sys.sql_expression_dependencies catches views and procedures naming it. A truly dead table has neither, a table with dependents needs its dependents judged first, children drop before parents when both are dead.

3

Rename before dropping, the cheap insurance: sp_rename each suspect to zzz_deprecated_originalname and wait two full weeks through at least one month end. Anything that screams identifies itself and renames back in seconds, no restore involved. Silence convicts.

4

Execute with an exit: full backup first, then script every victim's definition with SSMS Generate Scripts including indexes and constraints into source control, then the DROP TABLE statements in dependency order. The scripts mean even a table missed by the rename quarantine rebuilds structurally in a minute, with data recoverable from the backup.

The 2016 backup copies deserve one extra glance before the pipeline: occasionally the backup copy is the live table and the properly named one is the abandoned twin, five minutes with row counts and recent timestamps per pair settles which is which. Inherited databases keep that particular joke in stock.

The rename quarantine caught one table on day nine, a quarterly report nobody mentioned owned it. Everything else stayed silent and dropped cleanly this morning, 31 tables and 12GB gone with scripts and backup as the exit. Procedure adopted permanently.