I am trying to use the script to get unused Stored Procedure reports but having confusion as which one is the best approach as to look into cache or recompilation.
I don't want to run the script in each database and do the analysis.
Is it any way i can include in a one script and it's also gives me Database name, last access, scan or look up or not in cache to make easy for analysis.
I tried this one but i have to run for each database
WITH UnUsed (id)
FROM sys.procedures AS s
FROM sys.dm_exec_procedure_stats AS dm
SELECT s.name, s.type_desc
JOIN sys.procedures s ON unused.id = s.object_id
I also tried thiso one with hte counts but not sure to make decission
DatabaseName = DB_NAME(st.dbid)
,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid)
,StoredProcedure = OBJECT_NAME(st.objectid,dbid)
,ExecutionCount = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
AND cp.objtype = 'proc'
AND DB_NAME(st.dbid) NOT IN ('tempdb', 'msdb', 'master', 'model')
Anyone has better solution really appreciate it?
Thanks for your help!