How to check stored procedure modified date in SQL Server

This article is about SQL Server stored procedure modified date. In this topic, we will cover various information about the stored procedure creation or modification. For example, who created the stored procedure, when was it created or modified, etc.

How to check stored procedure modified date in SQL Server

If you want to know about the date and time when a user created or modified a stored procedure in SQL server 2019, To check stored procedure modified date in SQL, you can use either of the two system-defined views:

• sys.procedures: An object catalog view which we can use to know information about all the stored procedures created in the sql server database.
• sys.objects: A view which we can use to know information about all the objects created in the database.

Using sys.procedures

• If you want to know the creation and modification date of all the stored procedures, you can execute the following query;

USE <database name>
GO
SELECT [name], create_date, modify_date
FROM sys.procedures
ORDER BY modify_date DESC

• In our case, we are using the master database.

• We used the the ORDER BY clause to see the latest modification first.

• You can also find the creation and modification of a particular stored procedure. You can specify the name of the stored procedure while querying the sys.procedures view.

USE <database name>
GO
SELECT [name], create_date, modify_date
FROM sys.procedures
WHERE name= <procedure_name>

• The above query will return you the information about the specified stored procedure only.

Using sys.objects:

• This view stores inforamtion about all the database objects.
• You have to use the type= ‘P’ i.e. Procedure for getting only the stored procedure information.
• You can retrieve the stored procedure modified date using the following query:

USE <database name>
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

• This query will give you the same result as you saw in the sys.procedures view and give the information about all the stored procedures.
• In this view also, you can specify a name for getting the date information of a particular stored procedure.

USE <database name>
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P' AND name= <procedure_name>

Hence, using the above methods, you can get the creation and modification dates of the stored procedures in the SQL Server.

Read: SQL Server stored procedure parameters

How to check last execution time of stored procedure in SQL Server

In this section, we will discuss how you can check the last execution time of a stored procedure.

When you execute any stored procedure or function in SQL Server, the entry is temporarily stored in the cache. You can access the last execution time of a stored procedure in the cache. The sys.dm_exec_procedure_stats stores the performance statistics of a cached stored procedure.

You can use the below script to access the execution history of stored procedures:

Use master
GO
SELECT  
        SCHEMA_NAME(sysobject.schema_id) AS [Schema Name],
        OBJECT_NAME(stats.object_id) AS [Procedure Name], 
        stats.last_execution_time AS [Last Execution Time]
    FROM   
        sys.dm_exec_procedure_stats stats
        INNER JOIN sys.objects sysobject ON sysobject.object_id = stats.object_id 
    WHERE  
        sysobject.type = 'P'
    ORDER BY
           stats.last_execution_time DESC  

• The above code will return the execution history of all the stored procedures.
• If you want to get the execution history of a particular stored procedure, you can replace the where condition with the below code:

sysobject.type = 'P' AND sysobject.name= <stored procedure name>

• You can see the date and time when the procedure was last executed.

But, this method is not reliable. Because you can only get the execution history of the cached objects only. If the object is removed from the cache, you will not be able to access the execution history.

However, if you want a permanent solution, you can implement your own logic. You can create a log table for storing the execution history of stored procedures. You can put an Insert statement at the end of every stored procedure to insert the name of the stored procedure and the current date and time.

For example, if you have created a table as StoreLog, you can put an Insert statement like the following code at the end of every stored procedure:

INSERT INTO dbo.StoreLog(SP_Name, [Execution Time])
VALUES(<name of the current stored procedure>, GETDATE())

Thus, you might have learned various methods to track the execution history of stored procedures.

Read: SQL Server stored procedure vs function

You may also like reading the following topics.

• SQL Server create stored procedure
• SQL Server stored procedure insert into
• SQL Server stored procedure case statement
• SQL Server stored procedure naming convention
• Loop in SQL Server stored procedure
• Stored procedure in SQL Server for insert and update
• How to find who created a stored procedure in SQL Server

At the end of this article, you might be aware of various methods that you can use to find the information about the stored procedures in your database. You might have understood all the topics that we have discussed in this article, including the following:

• How to check stored procedure modified date in SQL Server
• How to check last execution time of stored procedure in SQL Server