Developping for the translation industry RSS 2.0

 Monday, February 01, 2010

This script will return a list of user tables in the database, along with the last time a SELECT was executed against them (including any views that include data from that table). This can be used to determine if a table is not in use anymore.

Statistics are reset when the SQL Server sevice is restarted, so this query will only return activity since that time. Also, just because there's no activity doesn't mean it's safe to remove an object - some tables may only be used during monthly or annual processes, and so they wouldn't show any activity except during those brief intervals.

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
         SELECT object_id      AS tablename,
                last_user_scan AS lastaction
         FROM   sys.dm_db_index_usage_stats u
         WHERE  database_id = Db_id
         SELECT object_id        AS tablename,
                last_user_lookup AS lastaction
         FROM   sys.dm_db_index_usage_stats u
         WHERE  database_id = Db_id
SELECT   Object_name
(so.object_id) AS tablename,
(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
ORDER BY Object_name


Other posts:

How to generate random numbers within a T-SQL query

SQL Server SPACE function

SQL Injection humor

Differences between temporary tables and table variables

How to track the growth of your database

How to get the total number of rows in a database

Using derived tables to boost SQL performance

Monday, February 01, 2010 10:36:17 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
Comments are closed.

About the author/Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2019
Stanislas Biron
Sign In
Total Posts: 135
This Year: 0
This Month: 0
This Week: 0
Comments: 1
All Content © 2019, Stanislas Biron