Find SQL Server Stored Procedure Create, Modified, Last Execution Date

As someone who looks after databases (Database Administrator or DBA), it’s really important to keep track of how well stored procedures are doing. Stored procedures are like pre-planned sets of instructions stored in a database. They’re helpful because they can make things run faster and safer. But sometimes, they might not work as well as they should. That’s where our T-SQL query comes in. It helps us check on the SQL Server Stored Procedure Create, Modified, Last Execution Date

The T-SQL Query

Let’s take a look at the special query we use to keep an eye on stored procedures:

-- This query retrieves information about a specific stored procedure including server name,
-- database name, schema name, procedure name, creation date, last modified date, and last execution time.

SELECT 
    -- Server name where the stored procedure resides
    SERVERPROPERTY('MachineName') AS [server name],
    
    -- Current database name
    DB_NAME() AS [database name],
    
    -- Schema name of the stored procedure
    [schema name] = SCHEMA_NAME([schema_id]),
    
    -- Name of the stored procedure
    o.name AS [procedure name],
    
    -- Creation date of the stored procedure
    o.create_date AS [creation date],
    
    -- Last modified date of the stored procedure
    o.modify_date AS [last modified date],
    
    -- Last execution time of the stored procedure
    ps.last_execution_time
FROM   
    sys.dm_exec_procedure_stats ps 
INNER JOIN
    sys.objects o ON ps.object_id = o.object_id 
WHERE 
    o.type = 'P'
    --AND o.schema_id = schema_name(schema_id)
    --AND o.name = 'xxxxxxxxxxxxxxxxx' -- Mention the SP name
ORDER BY
    ps.last_execution_time;

SQL

Conclusion

As DBAs, it’s our job to make sure everything in the database is running smoothly. By using queries like this one, we can keep track of how stored procedures are doing. This helps us catch any issues early on and keep things working well. Keeping an eye on stored procedures is just one of the many things we do to make sure databases run smoothly and safely.