Debido a diferentes razones en nuestras bases datos, para este caso SQL Server, se acumulan procedimientos almacenados que quedan en desuso, lo cual se traduce a su vez en riesgos de seguridad y problemas de control de código, por lo que debemos identificar cuáles son los procedimientos almacenados que no están siendo utilizados por nuestras aplicaciones.
La siguiente consulta nos muestra la fecha de ultima ejecución de un procedimiento almacenado.
USE MASTER;
GO
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp ([database_name] NVARCHAR(255), [procedure_name] NVARCHAR(255), last_execution_time DATETIME);
EXEC sp_MSForEachDB '
USE [?];
IF ''[?]'' NOT IN (''[master]'', ''[model]'', ''[msdb]'', ''[tempdb]'')
BEGIN
INSERT INTO #temp
SELECT
''[?]''
,p.name
,last_execution_time
FROM sys.procedures AS p
LEFT OUTER JOIN sys.dm_exec_procedure_stats AS s
ON p.[object_id] = s.[object_id]
WHERE SUBSTRING(p.name,1,3) not in (''dt_'',''sp_'')
ORDER BY p.name, s.last_execution_time;
END';
SELECT
[database_name]
,[procedure_name]
,[last_execution_time]
FROM #temp;
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
GO
La consulta itera en todas las bases de datos de la instancia en la cual se ejecute excluyendo las bases de datos del sistema, buscando la fecha de la última ejecución de los procedimientos almacenados de estas en la vista de administración dinámica sys.dm_exec_procedure_stats, excluyendo los procedimientos almacenados que tengan los prefijos dt_ (utilizados para soportar los diagramas de bases de datos) y sp_ (considerado una mala práctica por razones que todos conocemos) en sus nombres.
