Solved

sql 2005 - Last Time Table Used in Query

Posted on 2012-03-30
3
449 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Two tables - Sum of values - What is the difference 31 59
How to disable/enable multiple sql jobs in efficient way 11 134
SQL Backup skipping a few tables 7 62
Database maintenance 36 136
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

737 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