Finding Antique Tables

In doing some long overdue housekeeping I found that I needed to determine which tables are part of a legacy data staging solution that loads and renames tables and which are “temporary” backup tables created years ago. Knowing that the staged tables are processed and renamed nightly I figured finding the last modified date (DDL) of the table would help. So I wrote this script to:

/*(DDL) Last time table was altered or renamed*/
SELECT s.name as schema_name, o.name as table_name, o.type, o.type_desc, o.create_date, o.modify_date
FROM sys.objects o
join sys.schemas s on o.schema_id = s.schema_id
WHERE o.type = 'U'
ORDER BY o.modify_date desc

That got me thinking about other tables and wondering if they had been used recently or are they orphaned so I wrote this script to check and see when the data had been last modified (inserted, updated or deleted) and also added columns to see when it had last been selected since some look up tables don’t have much data churn.

/*(DML) Last time table was updated, inserted, deleted or selected from; data is not persistant over server restarts*/
SELECT s.name as schema_name,o.name as table_name, i.last_user_update, i.last_user_scan, i.last_user_seek, i.last_user_lookup
FROM sys.dm_db_index_usage_stats i
join sys.objects o on i.object_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
WHERE o.type = 'U'
ORDER BY s.name

You can check other objects by changing the type in the WHERE clause. The table below can help you find the object type in which you are interested.

type	type_desc
C	CHECK_CONSTRAINT
D	DEFAULT_CONSTRAINT
F	FOREIGN_KEY_CONSTRAINT
FN	SQL_SCALAR_FUNCTION
IT	INTERNAL_TABLE
P	SQL_STORED_PROCEDURE
PK	PRIMARY_KEY_CONSTRAINT
S	SYSTEM_TABLE
SQ	SERVICE_QUEUE
TF	SQL_TABLE_VALUED_FUNCTION
TR	SQL_TRIGGER
U	USER_TABLE
UQ	UNIQUE_CONSTRAINT
V	VIEW

I hope you find this useful.