sql 2005 - Last Time Table Used in Query

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.
dastaubAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
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
Bhavesh ShahLead AnalysistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dastaubAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.