:::: MENU ::::

Get list of Stored procedures or UDF with creation and modification dates

  • Apr 06 / 2011
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get list of Stored procedures or UDF with creation and modification dates

For monitoring and analysis purpose a broad view about creation and modification of SP and UDF in a database was required. I have created two scripts to get all SP and UDF with their creation and last modification dates.

/*  
 Get all User SP along with Creation and last modification dates  
 */  
 USE [DB_Name_here]  
 GO  
 SELECT name, create_date, modify_date  
 FROM sys.[objects]   
 WHERE type = 'P'  
 AND [is_ms_shipped] = 0  
 ORDER BY [create_date] desc, modify_date DESC   
 GO  

And in case of UDF they may be scalar, table valued or in-line table valued. You may generate the list of any type by providing following parameters for type column.
Scalar UDF = FN
Table valued = TF
In-Line Table Valued = IF

In following script i have included all three types in list of UDF.

/*  
 Get all UDF along with Creation and last modification dates  
 */  
 USE [DB_Name_here]  
 GO  
 SELECT name, create_date, modify_date, type  
 FROM sys.[objects]   
 WHERE type in ('FN','TF','IF')  
 AND [is_ms_shipped] = 0  
 ORDER BY type,[create_date] desc, modify_date DESC   
 GO  

For both of above scripts do not forget to provide/select required database name. Table used in these scripts is sys.Objects. Click here to get further details of columns and parameters in this table.

Consult us to explore the Databases. Contact us