Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

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 ?
0
alcindor
Asked:
alcindor
  • 3
  • 3
1 Solution
 
Aneesh RetnakaranDatabase 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
 
alcindorAuthor Commented:
Unfortunately, I did mean queried and not all tables are indexed so it looks like I'm out of luck.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Pawan KumarDatabase ExpertCommented:
So what is your next question now? Do you some issue
0
 
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
 
alcindorAuthor Commented:
As you say there is no such option in SQL server
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now