SQL Server is full of good stuff.  There are plenty of features to be used.  Plenty of applications to help it.  And there is even plenty of metadata within SQL Server to help you better understand your data and the queries that are being run.

It just so happens that a couple of clients requested some information on this additional metadata.  Both of the clients wanted something just a little different from the other.  After a little bit of thought, it came pretty clearly that what they wanted was definitely available within SQL Server.  The first client simply wanted to know the last time a procedure had been executed.  That is relatively easy enough – granted the procedure has been executed and granted the execution statistics have not been evicted.

The second client also wanted to know some information about the execution of a stored procedure.  But this time, they wanted to get the execution plan.  There are a few ways to trap an execution plan.  You could either run a trace, an XE session, or you could execute the query and grab the plan.  But if you didn’t already have an XE session running or a trace running and the client does not want you to execute the query to trap that plan?

Well, that is not a problem because SQL Server stores this stuff.  As long as the query and plan have not been evicted from the plan cache then you can get the metadata you desire.

Metadata to the Rescue

The metadata that we seek happens to be accessible through the use of several dynamic management views.  These are sometimes called dynamic management objects and are great to use to get to know your data and environment.  This time around, I am mostly interested in the following dynamic management objects: sys.dm_exec_query_stats , sys.dm_exec_cached_plans , sys.dm_exec_sql_text , and sys.dm_exec_query_plan.  I am using these objects because I want to trap execution stats along with the query SQL statement as well as the cached plan for that query.

So this is what I came up with to aid each of the clients in getting the data they sought.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
DECLARE @querysought VARCHAR(256) = '%boywhathairylegsyouhavethere%';
SELECT OBJECT_NAME(st.objectid) AS ObjName, qs.last_execution_time
, cp.size_in_bytes, cp.usecounts, qp.query_plan
,cp.cacheobjtype,cp.objtype
, st.text AS QueryText
,(SELECT st.text AS [processing-instruction(definition)]
            FROM sys.dm_exec_sql_text(qs.sql_handle) sti
            FOR XML PATH(''), TYPE) AS FormattedSQLText
FROM sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp
ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE OBJECT_NAME(st.objectid) LIKE @querysought
OR st.text LIKE @querysought
;

Pretty simple and straight forward.  Now, I have this quick query to find the last time a proc was run, as well as a means to trap the execution plan for that query.  If I run that query, I would be able to see something like the following.

I hope this is useful to you and hope you enjoy.