SQL Server | Find Created and Modified Stored Procedure

In this article i am going to show you how you can get list of all the created and modified stored procedure with it's Date and Time. also show you how you can get all the stored procedure from sys.objects. In sql server sysobjects Table contains one row for each object created within a database and it has a row for every constraint in sql server such as rule, log, stored procedure and etc in sql server database, So sys.bojects table is used to fetch all the info about the database.

In my previous articles i explained how to create and pass output parameter in stored procedure as well as how to execute stored procedure in sql server and also explained how to execute stored procedure from another stored procedure and how you can split comma delimited string in sql server. in this article i m gonna show you how to find name of procedures are created or modified from sys.objects table in sql server.

Implementation

So, Lets we write sql script to get list of all the stored procedure which is created or modified in current month.

SQL Script

SELECT  name AS ProcedureName ,
        CONVERT(VARCHAR(10), SysObj.modify_date, 103) AS [Create/Modify Date] ,
        CONVERT(VARCHAR(15), CAST(SysObj.modify_date AS TIME), 100) [Create/Modify Time]
FROM    sys.objects SysObj
WHERE   SysObj.type = 'P'
        AND DATEDIFF(D, SysObj.modify_date, GETDATE()) < YEAR(GETDATE())

Here, the condition of date in above sql script can be adjusted to retrieve required data based on need. i.g. If you wants to get all the name of procedure witch is created or modified in last 90 days,  then you just need to change your date condition shown as below.

SELECT  name AS Procedure Name ,
        CONVERT(VARCHAR(10), SysObj.modify_date, 103) AS [Create/Modify Date] ,
        CONVERT(VARCHAR(15), CAST(SysObj.modify_date AS TIME), 100) [Create/Modify Time]
FROM    sys.objects SysObj
WHERE   SysObj.type = 'P'
        AND DATEDIFF(D, SysObj.modify_date, GETDATE()) < 90

OR

SELECT  name AS ProcedureName ,
        CONVERT(VARCHAR(10), SysObj.modify_date, 103) AS [Create/Modify Date] ,
        CONVERT(VARCHAR(15), CAST(SysObj.modify_date AS TIME), 100) [Create/Modify Time]
FROM    sys.objects SysObj
WHERE   SysObj.type = 'P'
ORDER BY SysObj.modify_date DESC

This Sql Script will return all the name of procedure of your database date wise latest created or modified.

Explanation

 
If You analyzed above sql script then all the info are fetched from sys.objects table where sys.objects is used to fetch all the info from database. here we used type and date as a where condition to provide name of all the stored procedure which were created based on  entered date i.g. last 90 days, Last Month and etc and type indicates Object Types in sys.objects there where P is a code of Object type and that can be described as SQL Stored Procedure.

Output

Sql Server Find Created and Modified Stored Procedure

Summary

This article explains how to get list of all the stored procedure which is created or modified in sql server.