Technical Article

Last Accesses Stored Procedure

,

List of your Stored Procedures and when they were last accesses.

Change the database_name at the top.

Change the where cause to match your name convention.

examples: usp_Name , csp_Name, etc.

Use Database_Name
Go

SELECT  a.execution_count ,
    OBJECT_NAME(objectid) Name,
    query_text = SUBSTRING( 
    b.text, 
    a.statement_start_offset/2, 
    (CASE WHEN a.statement_end_offset = -1 
    THEN len(convert(nvarchar(max), b.text)) * 2 
    ELSE a.statement_end_offset 
    END - a.statement_start_offset)/2
    ) ,
    b.dbid ,
    dbname = db_name(b.dbid) ,
    b.objectid ,
    a.creation_time,
    a.last_execution_time,
    a.*
FROM    sys.dm_exec_query_stats a 
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b 
WHERE OBJECT_NAME(objectid) like 'sp%'
ORDER BY a.last_execution_time DESC

Rate

3.4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.4 (5)

You rated this post out of 5. Change rating