last table used in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

last table used in query

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 02:56:22

hi,

i'm looking for a best and faster way to retrieve last table used by a query by a specific user.

e.g.:

select * from MyTable
select @@rowcount

in this case @@rowcount returns number of rows affected by last query. so i'm looking for a way to retrieve table name similar as with @@rowcount.

thanks

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-12 : 14:21:12

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 14:47:46

thank you ConradK for your reply.
i'm looking only for table name.

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-12 : 15:00:43

select
*
,right(query,len(query)-charindex('from',query)+1)
from

(SELECT Top 30 deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC) q

Thats the hack-way I'de do it ;-P

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-12 : 16:41:11

Except there's no way to get user info from the plan cache and if the object is a stored proc, multi-query batch or query with multiple tables that query will return the first table.

If you want the last accessed table, try the index usage stats DMV, but again that's not per user.

--
Gail Shaw
SQL Server MVP

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-13 : 00:02:50

Gail thanks for additional information.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources