SQL Server stored procedure modified date

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 get SQL Server stored procedure modified date
• How to find who modified a stored procedure in SQL Server
• How to find who created a stored procedure in SQL Server
• How to find owner of stored procedure in SQL Server
• How to check last execution time of stored procedure in SQL Server

How to get SQL Server stored procedure modified date

If you want to know about the date and time when a user created or modified a stored procedure in SQL server 2019, 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: Stored procedure for search functionality in SQL Server

How to find who modified a stored procedure in SQL Server

When someone modifies or alters a stored procedure, the information is stored in logs. By accessing the logs you can know who modified the stored procedure and when.

We will discuss some easy ways to access the logs:

• Accessing Schema Changes History in SQL Server Management Studio.
• Accessing the trace file in SQL Server Profiler
• Using T-SQL query

But before using one of these methods, you have to give the necessary permissions to the Log Files. To give the necessary permissions, follow the steps below:

• Navigate to the SQL Server installation directory.
• The path to the log files should look like:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log

• The Log folder stores the log files created by the SQL Server.
• The log file that you require will have the .trc extension. For example, log_24.trc.
• The log file having a greater number in its name will be the most recent log file. For example. if we have two log files named log_26.trc and log_27.trc, the log_27.trc is the recent one and will contain the lastest modifications.
• Right click on the required log file, click on Properties and navigate to the Security tab and click on Advance.
• Click on Enable Inheritance and click on OK to save the changes.

Read: Find Store Procedure in SQL Server by Table Name

Accessing Schema Changes History in SQL Server Management Studio

You can track the schema changes in SQL Server Management Studio. Follow the below steps to access the Schema Changes History:

• Open SQL Server Management Studio and navigate to the SQL Server Instance name in the Object Explorer Window.
• Right click on the SQL Server Instance and click Reports > Standard Reports > Schema Changes History.

• Once you have accessed the Schema Changes History, you can find the procedure name and the information about its modification in the logs.

• In our case, we modified the addition stored procedure.
• You can check the Login Name column to determine who modified the stored procedure.

Read: Try catch in SQL Server stored procedure 

Accessing the log file in SQL Server Profiler

You can also access the log files using the SQL Server Profiler. We have installed the SQL Server Express Edition and the SQL Server Profiler was automatically installed with the SQL Server.

• To access the log files, navigate to the SQL Server installation directory in the Windows Explorer.
• The path should be something like this:

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log

• Open the desired log file with SQL Server Profiler.
• You can see the stored procedure name in the ObjectName column and the name of the user who modified the stored procedure name in the LoginName column.

Using T-SQL query

You can use also access the log files using the T-SQL queries. The advantage of this method is that you can access only the required information from the log. Also, you can use some specific information in your programming language. For example, create a stored procedure to access some specific information.

We can use the sys.fn_trace_gettable function to access the log files. For example, to access the Login Name and modification date, you can write the T-SQL query as:

SELECT ObjectName, StartTime, LoginName 
FROM sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log\log_27.trc', default)
WHERE ObjectName= 'addition'

Note: Edit the Log File path in the above query.

Hence, you might have learned the various methods to find out who modified the stored procedure and when.

Read: SQL Server stored procedure parameters

How to find who created a stored procedure in SQL Server

In the above section, we have discussed how to access various information in the log files of the SQL Server Instance. You can use the SQL Server Profiler to access the log information.

You can also find the user who created the stored procedure using the Schema Changes History in the SQL Server Management Studio. You can find the name of the user in the LoginName column of the log files.

However, you can also execute the following T-SQL query to make your task simple:

SELECT ObjectName, LoginName , StartTime
FROM sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log\log_27.trc',default)
WHERE EventClass= 46 and ObjectName= 'ProcedureSales'

• Replace the path of the log file and name of the stored procedure that you want to search for in the above query.
• EventClass = 46 refers to the creation of object.
• LoginName is the user who created the stored procedure.

Thus, you might have learned how you can find who created a particular stored procedure in SQL Server.

Read: SQL Server stored procedure return value

How to find owner of stored procedure in SQL Server

In this section, you will learn how you can find the owner of a stored procedure in SQL Server.

The sp_stored_procedures is a system-defined stored procedure that returns some basic information about the stored procedures. You can use this procedure to find the owner of a stored procedure. Executing it will give you the output like the below image.

EXEC sp_stored_procedures 

If you want to find the owner of a particular stored procedure, you can pass the name of the stored procedure as an input parameter. For example, if I want to know the owner of a stored procedure named Circle, I will write the query as:

EXEC sp_stored_procedures 'Circle

Thus, you might have learned how you can find the owner of a stored procedure in SQL Server.

Read: SQL Server stored procedure vs function

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.

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

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 get SQL Server stored procedure modified date
• How to find who modified a stored procedure in SQL Server
• How to find who created a stored procedure in SQL Server
• How to find owner of stored procedure in SQL Server
• How to check last execution time of stored procedure in SQL Server