Find Created and Modified Stored Procedure in SQL

In this article, I’m going to show you how you can get a list of all stored procedures created and modified with their date and times. Also, show you how you can get all stored procedures sys. stuff.

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

In my previous articles, I explained how to do this Create and pass output parameters in stored procedures as well as how Execute a stored procedure in SQL Server And also explained how to do it execute the stored procedure from another stored procedure and how can you A comma-separated string is split in SQL Server. In this article, I’ll show you how to find the names of procedures that have been created or changed sys. stuff A table in SQL Server.

Application

So, let’s write a SQL script to get a list of all stored procedure which was 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 conditions in the above SQL script can be adjusted to retrieve required data as needed. ig If you want to get all the names of the procedure created or modified in the last 90 days, 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

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 procedure names of your database by the date it was last created or modified.

explanation

If you parse the SQL script above, all the information will be fetched from the sys.objects Table where sys.objects are used to fetch all the information from the database.

We used type and date as the where condition to provide the name of all stored procedures created based on the date entered in the last 90 days, last month, etc., and the type specifies object types in sys.objects there were P It is code of type Object and can be described as SQL stored procedure.

Productivity

Summary

This article explains how to get a list of all stored procedures that are created or modified in SQL Server.