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.
