May 24, 2012 at 1:00 am
Q: How to see the last execution date of database objects in sql server 2005 ????
I used
select t.name
,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() and t.is_ms_shipped = 0
order by t.name
But I am able to get only tables last accessed.
I want SP's, Functions, Views etc,, Respectively.
Can somebody Help.....????
Have some better way to find it.
May 24, 2012 at 1:04 am
Even I tried,,
SELECT qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,
qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text like '%AllReqMailGet%'
I am getting ERROR:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_exec_text_query_plan'.
Have some script to find the details ???
Please post ASAP..
Thanks in advanced
May 24, 2012 at 2:37 am
There's no script that will get you that information. You can look in the plan cache (sys.dm_exec_query_stats/sys.dm_exec_cached_plans) and some objects will have an entry there. For those that don't, there's no way to tell when they were last used without some custom monitoring.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2012 at 8:41 am
Thanks,
How can I identify the Objects from Plan_handle from (sys.dm_exec_query_stats/sys.dm_exec_cached_plans)
May 24, 2012 at 1:16 pm
This may help:
SELECT TOP 10
DB_NAME(x.dbid) AS db,
OBJECT_SCHEMA_NAME(x.objectid, x.dbid) AS schemaname,
OBJECT_NAME(x.objectid, x.dbid) AS objectname,
LEFT(x.text, 100),
p.*
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) x;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply