Introduction

As our database evolves and applications change, we may encounter unused tables that are no longer required for our system’s functionality. In these cases, it is crucial to handle such tables with care to avoid any unintended data loss or disruptions. In this blog, we will explore a safe and systematic approach to drop an unused table by renaming it and retaining the renamed tables for a defined period. This approach ensures that we have a grace period to verify the changes before permanently dropping the table.

Step 1: Identifying the Unused Tables

The first step is to identify the table that we want to drop. By using the below script, we can list the table that we want to drop (Unused tables). We are fetching the unused tables details from usage of index stats.
with UnUsedTables (schema_name,TableName , CreatedDate , LastModifiedDate )
AS (
SELECT distinct
a.name as Schema_name,
DBTable.name AS TableName
,DBTable.create_date AS CreatedDate
,DBTable.modify_date AS LastModifiedDate
FROM sys.all_objects DBTable
JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
join sys.schemas a on a.schema_id = DBTable.schema_id
WHERE DBTable.type ='U'
AND NOT EXISTS (SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT distinct Schema_name, TableName , CreatedDate , LastModifiedDate
FROM UnUsedTables

Step 2: Renaming the Unused Tables

Instead of dropping the unused tables immediately, consider renaming it with a suffix or prefix that signifies its status as “to_be_deleted”. This prevents any accidental references or dependencies from breaking during the grace period.

Script to rename the tables.
declare @sql nvarchar(400)
declare @table_name varchar(100)
declare @stat varchar(100)
declare @counter int
set @counter=1
declare @max int
set nocount on
--Create a table to store all values
IF OBJECT_ID(N'tempdb.dbo.#rebuild_table_ssspl', N'U') IS NOT NULL
DROP TABLE #rebuild_table_ssspl

IF OBJECT_ID(N'tempdb.dbo.#temp', N'U') IS NOT NULL
DROP TABLE #temp

IF OBJECT_ID(N'dbo.tbl_to_be_deleted', N'U') IS NOT NULL
DROP TABLE tbl_to_be_deleted

SELECT distinct
a.name as nam,
DBTable.name AS TableName
,DBTable.create_date AS CreatedDate
,DBTable.modify_date AS LastModifiedDate
into #temp
FROM sys.all_objects DBTable
JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
join sys.schemas a on a.schema_id = DBTable.schema_id
WHERE DBTable.type ='U'
AND NOT EXISTS (SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = DBTable.object_id )

select distinct row_number() over(order by tablename) id, nam, TableName into #rebuild_table_ssspl from
#temp

——————————————————————–
select row_number() over(order by tablename) id, nam+'.'+tablename+'_to_be_deleted' as Sch_tblname, tablename,
CreatedDate, LastModifiedDate into tbl_to_be_deleted
from #temp

——————————————————————
--where table_type<>'VIEW'
set @max = (select count(*) from #rebuild_table_ssspl)
while (@counter <= @max)
begin
set @stat =(select nam from #rebuild_table_ssspl where id=@counter)
set @table_name = (select tablename from #rebuild_table_ssspl where id=@counter)
--print @table_name
set @sql= 'Exec sp_rename '''+@stat + '.' +@table_name + ''' , ''' +@table_name + '_to_be_deleted'';'
print @sql
--exec (@sql)  --(Uncomment to rename the table)
set @counter = @counter+1
end

Step 3: Setting a Retention Period

Decide on an appropriate retention period based on your organization’s policies and business requirements. Typically, a week is sufficient to allow adequate time for testing and verification.

Step 4: Monitor Application Functionality

During the retention period, closely monitor your applications and queries to ensure that they continue to work correctly with the renamed table. Perform extensive testing to identify any issues related to the table renaming.

Step 5: Backup and Rollback Plan

Before proceeding further, take a full backup of your database to create a recovery point. In case any unexpected problems arise during the grace period, you can quickly revert to the previous state by restoring the backup.

Step 6: Final Confirmation

Once the retention period elapses and we are confident that the system functions correctly with the renamed table, proceed with dropping the table permanently.

select 'drop table '+Sch_tblname from tbl_to_be_deleted

Step 7: Document the Process

Ensure you document the entire process, including the table renaming, retention period, and final table drop. Proper documentation is essential for future reference and to ensure other team members are aware of the changes.

Conclusion

Dropping an unused table requires a cautious and systematic approach to avoid unintended consequences. By renaming the table and setting a retention period, we provide ample time to verify the changes and ensure the system functions as expected without the table. Regularly monitoring and testing during this period further enhances confidence in the process. Rember to create backups and maintain documentation to support future decisions and understanding of the database’s evolution. Following this method, we can streamline our SQL Server and maintain a clean, efficient, and up-to-date database environment.

pramodh.p