T-SQL Script: Unused tables in a database (last select)

I’ve found another great addition to your cookbook (Source). It’s simple, but effective 🙂


WITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)

SELECT
r.TableName
,r.LastSelect
FROM
(
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
--AND so.object_id 100
GROUP BY OBJECT_NAME(so.object_id)
) AS r
--WHERE r.TableName LIKE '%TableName%'
ORDER BY r.TableName

Query result:

Unused-tables-last-select

Advertisements

One thought on “T-SQL Script: Unused tables in a database (last select)

  1. great code but it will only bring back LastSelect time for tables that have PK or Index since it’s looking at sys index stats table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s