http://www.sqlservercentral.com/blogs/robert_davis/2008/03/28/the-shadow-knows-what-procedures-lurk-in-the-sql-plan-cache/

Printed 2014/10/21 01:23AM

The Shadow Knows What Procedures Lurk in the SQL Plan Cache

By Robert Davis, 2008/03/28

How do you know what procedures are cached in SQL Server? Simple, just ask, and SQL Server will tell you.You can query the SQL Server dynamic management views to get a list of procedures in cache. In this example, I query sys.dm_exec_cached_plans and sys.dm_exec_sql_text:

Select object_name(st.objectid, st.dbid) As ProcedureName
From sys.dm_exec_cached_plans cp
Cross Apply sys.dm_exec_sql_text(cp.plan_handle) st
Where cp.objtype = 'Proc'
And st.dbid = db_id();

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.