Q: How to write a query to display unused stored procedures?

Question

How to write a query to display unused stored procedures?

Answer 1

You can use below query to find all procedures which are not altered in last one year.

select ROUTINE_NAME,LAST_ALTERED 
from INFORMATION_SCHEMA.ROUTINES
where LAST_ALTERED < DATEADD(YY,-1,GETDATE())

You can use below query to find all procedures which are not executed in last one year.

SELECT o.name, 
ps.last_execution_time,DB_NAME(ps.database_id)
FROM   sys.dm_exec_procedure_stats ps 
INNER JOIN sys.objects o 
ON ps.object_id = o.object_id 
WHERE  DB_NAME(ps.database_id) = 'DATABASENAME' 
and  cast(ps.last_execution_time as DATE)< DATEADD(YY,-1,GETDATE())