Solved

sql 2005 - Last Time Table Used in Query

Posted on 2012-03-30
3
454 Views
Last Modified: 2012-03-31
Does SQL store the last date-time a table was part of a query?  

I'm trying to determine which tables are not being used and I would like to not bother asking about tables used on a daily basis, but ask about tables not used in months.
0
Comment
Question by:dastaub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 37789573
Not directly.  However, some of this can be derived from the standard reports by database.  Use the Index Usage Statistics report as a good starting surrogate for this - if there is a table that hasn't had any index usage (assuming the table has an index), it likely has not been touched for the same time.  This value is only maintained since the last restart of the SQL Server, though.
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 37790034
Hi,

Check out this link.

this will help you.

Sample code

USE AdventureWorks
GO
CREATE TABLE Test
(ID INT,
COL VARCHAR(100))
GO
INSERT INTO Test
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO


SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test')

Open in new window


Link
http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/


- Bhavesh
0
 

Author Closing Comment

by:dastaub
ID: 37792314
Below is code from supplied link.  It can get the data needed.
select
 t.name
 ,user_seeks
 ,user_scans
 ,user_lookups
 ,user_updates
 ,last_user_seek
 ,last_user_scan
 ,last_user_lookup
 ,last_user_update
 from
 sys.dm_db_index_usage_stats i JOIN
 sys.tables t ON (t.object_id = i.object_id)
 where
 database_id = db_id()
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question