DB 리뉴얼 할 일이 있어서 SP 사용하지 않은 프로시져들이 먼가~ 찾는 DMV를 이용해서 쿼리를 만들어 보았습니다.

 

Script

WITH exec_procedure_stats
AS
(
	SELECT 
		object_id
		, database_id = MAX(database_id)
		, execution_count = SUM(execution_count) 
		, min_elapsed_time = SUM(min_elapsed_time) 
		, total_elapsed_time = SUM(total_elapsed_time) 
		, max_elapsed_time = SUM(max_elapsed_time) 
		, last_elapsed_time = SUM(last_elapsed_time) 
		, cached_time = MAX(cached_time) 
	FROM sys.dm_exec_procedure_stats
	GROUP BY object_id
)
SELECT 
	[DB Name] = DB_NAME(),
	[SP Name] = sc.name + '.'  + p.name, 
	[ISDELETE] = CASE WHEN qs.execution_count IS NULL THEN 1 ELSE 0 END,
	qs.execution_count, qs.min_elapsed_time,
	qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
	qs.max_elapsed_time, qs.last_elapsed_time,  qs.cached_time	
FROM sys.procedures AS p 
	LEFT OUTER JOIN exec_procedure_stats AS qs
		ON p.[object_id] = qs.[object_id]
			AND qs.database_id = DB_ID()
	INNER JOIN sys.schemas AS sc
		ON p.schema_id = sc.schema_id
WHERE p.is_ms_shipped = 0
ORDER BY sc.name ASC, p.name ASC
OPTION (RECOMPILE);
GO

결과