Skip to navigation
Way to log the store procedures with MSSQl
28.03.24
```tsql drop TABLE #_p SELECT '231' AS [server] ,'SL' AS [db] ,r.name AS stored_procedure --,r.[object_id] ,ISNULL(CONVERT(VARCHAR(8), ps.last_execution_time, 112),0) AS last_execution_day INTO #_p FROM [S231].[sl].sys.objects as r LEFT JOIN [S231].[sl].sys.dm_exec_procedure_stats ps ON ps.database_id = '14' AND ps.object_id = r.object_id WHERE r.type_desc = 'SQL_STORED_PROCEDURE' AND r.name like 'zz_%' AND ps.last_execution_time IS NOT NULL ORDER BY r.name INSERT INTO sl_h.dbo.sp_log([server],db,stored_procedure,[last_execution_day]) SELECT p.[server] ,p.db ,p.stored_procedure ,p.last_execution_day FROM #_p AS p LEFT JOIN sl_h.dbo.sp_log AS l ON p.stored_procedure = l.stored_procedure WHERE l.stored_procedure IS NULL UPDATE l SET l.last_execution_day = p.last_execution_day FROM sl_h.dbo.sp_log AS l LEFT JOIN #_p AS p ON l.stored_procedure = p.stored_procedure WHERE p.stored_procedure IS NOT NULL AND p.last_execution_day != l.last_execution_day ```
Reply
Anonymous
Information Epoch 1735230931
Data dominates.
Home
Notebook
Contact us