Developping for the translation industry RSS 2.0



 Monday, 01 February 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
(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   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)
ORDER BY Object_name
(so.object_id)

 

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, 01 February 2010 10:36:17 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Code Snippet | SQL
Comments are closed.

Navigation
Advertisement
About the author/Disclaimer

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

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