How to find the created and last updated date of procedures in a SQL instance

Introduction 

This T-SQL script will demonstrate how to find the created and last updated date of stored procedures in a SQL Server instance.

Scenarios

As some people asked how to get the created and last updated date of stored procedures in a specific database or an instance, this script will provide some help for us. The script contains a stored procedure which needs two parameters: a database name and an instance name. If just database name is provided, the stored procedures in the database will be output. If no parameters are provided, the script will output all the stored procedure in the instance.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a "New Query", copy the code from CreateUpdateSPInfo.sql, paste it and run.

3. Execute the stored procedure.
Note: @DatabaseName and @InstanceName can be null
For example: EXEC CreateUpdateSP InsideTSQL2008

After the script and example finishes running, we'll get the following figure:

Here are some code snippets for your reference.

WHILE((SELECT COUNT(*) FROM @tablename) <> 0)
BEGIN    
    SET @DBName = (SELECT TOP 1 StoredTableName FROM @tablename)            
    DECLARE @cmd2 nvarchar(400)
    SET @cmd2 = '
    SELECT SPECIFIC_CATALOG AS DatabaseName,SPECIFIC_NAME AS SPName,CREATED AS CreatedTime,LAST_ALTERED AS LastUpdatedTime FROM[' +@DBName+'].[information_schema].[routines]
    WHERE routine_type = ''PROCEDURE'''
    PRINT @cmd2
    DELETE FROM @tablename WHERE Number = @i + 1
    SET @i = @i + 1
EXEC(@cmd2)    
END

WHILE((SELECT COUNT(*) FROM @tablename) <> 0) 
BEGIN     
    SET @DBName = (SELECT TOP 1 StoredTableName FROM @tablename)             
    DECLARE @cmd2 nvarchar(400) 
    SET @cmd2 = ' 
    SELECT SPECIFIC_CATALOG AS DatabaseName,SPECIFIC_NAME AS SPName,CREATED AS CreatedTime,LAST_ALTERED AS LastUpdatedTime FROM[' +@DBName+'].[information_schema].[routines] 
    WHERE routine_type = ''PROCEDURE''' 
    PRINT @cmd2 
    DELETE FROM @tablename WHERE Number = @i + 1 
    SET @i = @i + 1 
EXEC(@cmd2)     
END 

Prerequisites

SQL Server 2005 or higher version

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.