Analysis of table use

I am using Microsoft SQL Server 2005. I need to know the most recent date/time  when each table was last opened. Is there a utility or query that can provide this information ?
LVL 2
Roger AlcindorAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
What you mean by opened ?  if it is last queried, you are not lucky, there is no such option in sql server . If it is last time the table structure was modified, you can check the sysobjects table.
0
 
Pawan KumarDatabase ExpertCommented:
Use below query to get the info.

Note - Groups is the table Name-

--

SELECT 
name TableName, create_date CreatedDate ,modify_date ModifiedDate, *
FROM SYS.TABLES
WHERE name = 'Groups'


--

Open in new window



o/p

TableName      CreatedDate      ModifiedDate      name      object_id      principal_id      schema_id      parent_object_id      type      type_desc      create_date      modify_date
Groups      2016-11-01 18:02:53.963      2016-11-01 18:02:53.963      Groups      267147997      NULL      1      0      U       USER_TABLE      2016-11-01 18:02:53.963      2016-11-01 18:02:53.963


Try2... for more details.. you should use sys.dm_db_index_usage_stats

--

SELECT DB_NAME(k.[database_id]) DB, OBJECT_NAME(k.[object_id]) TableName, MAX(k.[last_user_lookup]) LastLookUp, MAX(k.[last_user_scan]) LastScan,
MAX(k.[last_user_seek]) LastSeek FROM sys.dm_db_index_usage_stats AS k
WHERE k.[database_id] = DB_ID()
AND k.[object_id] = OBJECT_ID('Groups')
GROUP BY k.[database_id], k.[object_id];


--

Open in new window


o/p

DB                              TableName      LastLookUp      LastScan                              LastSeek
ABCInvestment      Groups               NULL              2016-11-01 18:46:40.443      NULL


Hope it helps !!

Let me know if you need more info here.
0
 
Roger AlcindorAuthor Commented:
Unfortunately, I did mean queried and not all tables are indexed so it looks like I'm out of luck.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Pawan KumarDatabase ExpertCommented:
So what is your next question now? Do you some issue
0
 
Roger AlcindorAuthor Commented:
No further question, as you said "if it is last queried, you are not lucky, there is no such option in sql server".
0
 
Pawan KumarDatabase ExpertCommented:
Great, if you have no further questions.

Regards,
pawan
0
 
Roger AlcindorAuthor Commented:
As you say there is no such option in SQL server
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.