daily pastebin goal
10%
SHARE
TWEET

Untitled

SQLSoldier Oct 10th, 2014 248 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @TABLE SYSNAME,
  2.         @Schema sysname;
  3.  
  4. SET @TABLE = QUOTENAME(N'Product');
  5. SET @Schema = QUOTENAME(N'dbo');
  6.  
  7. WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sqlx)
  8. SELECT object_name(st.objectid, st.dbid) AS ObjectName,
  9.     qp.query_plan AS QueryPlan,
  10.     st.text AS ObjectText
  11. FROM sys.dm_exec_cached_plans AS cp
  12. OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
  13. OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
  14. WHERE qp.dbid = DB_ID()
  15. AND qp.query_plan.exist('//sqlx:Object[@Schema=sql:variable("@Schema")]') = 1
  16. AND qp.query_plan.exist('//sqlx:Object[@Table=sql:variable("@Table")]') = 1;
RAW Paste Data
Top