How to get Stored Procedure modification history in SQL Server?

Question

I want to write a query to get the result which should contain all the modifications details of a stored procedure like LastModifiedTime, LastModifiedBy (we use windows authentication) and if possible to get the information on what was modified in that particular modification.

I have written the below query but it is not sufficient, it gives only the information of last modified, please guide me

select 
[database name] = db_name() 
,[schema name] =  SCHEMA_NAME([schema_id])
,name [stored proc name]
,create_date [create date]
,modify_date [last modify date]
from sys.objects
where type = 'P'
and name = 'spName'

Answer 1

You can use a DDL trigger to create a log of changes.

See https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

However, this would be useful only for changes that will happen after you will create the DDL trigger.