TSQL - How To Check When Object ( Table, Stored Procedure, Trigger etc) was Modified Last Time

Scenario: 

We as SQL Server developers send our DDL Scripts ( Add New Column, Drop some column from table, or Alter Stored Procedure) to DBA. After deployment we verify those changes and sometime it happens that the changes are not done. Maybe DBA has missed one of the script to run. If we can get the last modified date for object ( Table,Stored Procedure, View etc) that can confirm the changes are done on given date.


Solution: 

 We can use below query to find out last modified date for Object( Table, Function, Trigger, Stored Procedure etc)

SELECT name,
       create_date,
       modify_date FROM   sys.objects
ORDER  BY modify_date DESC

Or we can provide the name of object for which we want to know the last modified date.

SELECT name,
       create_date,
       modify_date FROM   sys.objects 
WHERE name='ObjectName'

Fig 1: Get the Last Modified DateTime for an Object in SQL Server 



Video Demo:
How to Check the last Modified Datetime of an Object in SQL Server Database