SQL Server: How to get list of update date of specific store procedure

Question

this script i know which return last update date of specific store procedure.

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'USP_Generate_CSM_New'

but my requirement is to know all the update date of a specific SP or all the update date of last 3 moths of specific SP.

also i have to know all update date and user login name, user pc name or IP address from where SP was updated.

please guide me. thanks

Answer 1

Hi @T.Zacks
When someone modifies or alters a stored procedure, the information is stored in logs. By accessing the logs you can know who modified the stored procedure and when.

    SELECT ObjectName, StartTime, LoginName   
    FROM sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log\log_27.trc', default)  
    WHERE ObjectName= 'USP_Generate_CSM_New'  

Please refer to this article for more details：SQL Server stored procedure modified date

Best regards,
LiHong

Answer 2

but my requirement is to know all the update date of a specific SP

If you haven't implemented a logging, then you can not get the required information.
See DDL Triggers https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15