Many times during production/development, if something is not working we would like to check when the object (Stored Procedure, Function, Table) was last modified. Just to make sure we have right version on our environment.


Hopefully this will help you. Lets Take a look, Here are steps :

 

 

1. Open SSMS (Microsoft SQL Server Management Studio)   

 

 

2. Connect to the intended SQL Server instance   

3. Select the Database and Run the query

You'll want to highlight the database that you are interested in and select the New Query button in the toolbar. In the Query window, insert the following TSQL:

--Here we are using AdventureWorks  database

USE [AdventureWorks]

GO

SELECT name, create_date, modify_date, type, type_desc

FROM sys.objects

GO

3. Querying For Particular Type Of Object

--Here we are using AdventureWorks  database

USE [AdventureWorks]

GO

SELECT name, create_date, modify_date, type, type_desc

FROM sys.objects

WHERE type ='P'    

/****

Here we are filtering object of type Stored Procedure --(i.e. type_desc ='SQL_STORED_PROCEDURE')

***/

GO