Find Created and Modified Stored Procedure in SQL

In this article, I am going to show you how you can get a list of all the created and modified stored procedures with their date and times. also, show you how you can get all the stored procedures from sys. objects. 

In an SQL server sysobjects Table contains one row for each object created within a database and it has a row for every constraint in SQL servers such as rules, logs, stored procedures and etc in SQL server database, So sys. projects table is used to fetch all the info about the database.

In my previous articles, I explained how to create and pass output parameters in stored procedures as well as how to execute a stored procedure in an SQL server and also explained how to perform the 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 the names of procedures created or modified from sys. objects table in SQL server.

Implementation

So, Lets we write an SQL script to get a list of all the stored procedure which is created or modified in the 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 date condition in the above SQL script can be adjusted to retrieve required data based on need. i.g. If you want to get all the names of the procedure which been created or modified in the last 90 days,  then you just need to change your date condition shown 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

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 names of procedures of your database date wise latest created or modified.

Explanation

If You analyzed the above SQL script, all the info is fetched from the sys.objects table where sys.objects are used to fetch all the info from the database. 

We used type and date as a where condition to provide the name of all the stored procedures 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 a list of all the stored procedure which is created or modified in the SQL server.