Identificar ultima ejecución de procedimientos almacenados en las bases de datos de una instancia SQL Server

Por: Alfredo Arias

Problema

Debido a diferentes razones en nuestras bases datos 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 y tomar medidas administrativas en este sentido 

Solución

La siguiente 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, debemos de tener en cuenta que esta vista solo muestra este dato en relación a la última vez que nuestra instancia se reinició, por lo tanto la información que obtendremos será relativa a esta fecha y puede que nos muestre que muchos de los procedimientos no han sido ejecutados nunca, lo cual en realidad significa que no se han ejecutado desde la última vez que se reinició la instancia, independientemente de esta situación, la información que obtenemos de esta consulta es muy útil ya que nos dará una idea del comportamiento de uso de nuestros procedimientos almacenados y nos ayudara a tomar decisiones de control del código esta en uso lo cual es un tema de seguridad a nivel de código muy importante.

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


Referencias

• MSDN :: sys.dm_exec_procedure_stats (Transact-SQL)
• MSDN :: Uso del prefijo "SP_" en el nombre de procedimientos almacenados
• databases.aspfaq.com :: What are all these dt_ stored procedures, and can I remove them?
• LessThanDot :: Sp_MSforeachDB