Find SQL Server Stored Procedure Create, Modified, Last Execution Date and Code

By:   |   Comments (6)   |   Related: > Stored Procedures


Problem

I have recently been tasked with tracking and monitoring the SQL Server stored procedure objects in the databases that I administer.  Please provide code samples that enumerate the stored procedures in a SQL Server database as well as the T-SQL code that defines them.  Also, present code samples showing how I can track the creation and last modified dates for stored procedures as well as when they last ran.

Solution

SQL Server stored procedures can implement vital functions within a database.  As you work on designing a new database or assume administrative and development responsibilities for a currently existing database, you are likely to need to track and monitor the performance of many object types in a database, including stored procedures.  This tip demonstrates the use of key views that can facilitate these tasks.

You will learn:

  • a couple of different ways to enumerate the stored procedures in a database by schema
  • to display the T-SQL that defines what a stored procedure does
  • to retrieve the initial creation date and the last date when a stored procedure was modified
  • to show when a cached stored procedure last ran

Three special types of views help to enable these kinds of tasks.

  • Microsoft introduced catalog views with SQL Server 2005 to control access to SQL Server metadata – data about the objects in SQL Server.  You can learn more about catalog views and get references to other tips about metadata from this prior tip.
  • Information_schema views were initially introduced with the same version of SQL Server.  Erland Sommarskog explains that these views are ANSI (American National Standards Institute) compliant and build on catalog views within SQL Server.  MSSQLTips.com offers a tutorial on information_schema views.
  • Dynamic Management Views are particularly useful for monitoring the performance of database components, such as stored procedures.  MSSQLTips.com also offers a tutorial on dynamic management views.

Enumerating the Stored Procedures in a SQL Server Database

Perhaps the easiest way to get started enumerating the stored procedures in a database is with sys.procedures, which is a catalog view. 

The sys.procedures catalog view provides a SQL Server specific way of querying information about metadata, such as stored procedure object name and schema name.  It is also relevant to recall that stored procedures reside in a database.  Therefore, a stored procedure within SQL Server instance is uniquely identified by the combination of database name, schema name, and object name.

The following script demonstrates one approach for enumerating the names of stored procedures in a database.  The script starts with a use statement that sets the default database context.  The select statement after the use statement queries sys.procedures.  This view can return a row for each stored procedure in a database.  Three columns are reported via the select statement.

  • The db_name function returns the name of the default database context.
  • The schema name is calculated by the return value of the schema_name function from the schema_id value for a stored procedure’s row in the sys.procedures view. Starting with SQL Server 2005, stored procedures with the same name can reside in a database within different schemas.
  • The name column value from sys.procedures returns the name of a stored procedure object within a schema within a database.
  • The where clause restricts output to stored procedures coded in T-SQL; other type_desc options include: Extended stored procedure, Replication-filter-procedure, and Assembly (CLR) stored-procedure.
use CodeModuleTypes
go
 
-- list stored procedures with database name and schema name
-- from sys.procedures
select 
 db_name() [database name]
,[schema name] = SCHEMA_NAME([schema_id])
,name [stored proc name]
FROM sys.procedures
where type_desc = N'SQL_STORED_PROCEDURE'
order by name

Here’s the output from the preceding script.

  • Database name for all stored procedures is CodeModuleTypes because of the use statement.
  • For the stored procedures in this tip, the schema name for all stored procedures is dbo.  It is possible, some may say a best practice, to use a rich schema structure of multiple schemas within a database.  For the sake of simplicity, this practice is not followed here.
  • The stored proc name column is from sys.procedures.  The name column values are listed in alphabetical order because of the order by clause at the end of the preceding script.
query results

Another approach to enumerating stored procedures in a database is via information_schema views.  Information_schema views are not specific to SQL Server (recall that they are ANSI compliant).  When you use information_schema views, developers familiar with other database products besides Microsoft SQL Server will more readily understand your code, and the likelihood of your code working across multiple database platforms will increase.

Here’s a query for an information_schema view that lists all the stored procedures in a database.  It presumes a preceding use statement, such as for CodeModuleTypes.  Alternatively, you can precede the view name with the database name in the from clause, such as CodeModuleTypes.information_schema.routines instead of just information_schema.routines.

  • Specific_catalog denotes the database name.
  • Specific_schema returns the schema name in which the stored procedure resides.
  • Specific_name returns the name of the stored procedure object.
  • Additionally, a where clause designates ‘procedure’ as the type of routine to be listed.  A routine_type value of procedure enables the selection of stored procedures.  A routine_type value of function enables the selection of user-defined functions.
-- list stored procedures with catalog name and schema name
-- from information_schema.routines
select 
 specific_catalog [database name]
,specific_schema [schema name]
,specific_name [stored proc name]
from information_schema.routines  
where routine_type = 'procedure'
order by specific_name

The use of the specific_schema column in the preceding script deserves some comment because this SQL Docs page remarks that "** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of an object is to query the sys.objects catalog view."  Nevertheless, testing performed for this tip resulted in valid schema values from information_schema views for stored procedures.  At least one other published source confirms that the SQL Docs assertion may have been correct for SQL Server 2005, but is no longer valid.

Here’s a display with the results set for the information_schema.routines output enumerating stored procedures.  Notice that the results exactly match those from the sys.procedures catalog view, including the column of schema name values.

query results

Displaying the defining T-SQL code for a SQL Server Stored Procedure

Next, lets switch our focus to discovering the T-SQL code for a stored procedure.  Displaying the code for a stored procedure again varies depending on if we are using custom SQL Server features or a generic information_schema approach.

The following script uses SQL Server built-in custom functionality for displaying the defining T-SQL for a stored procedure.  The object_id column value from a sys.procedures view is a number that can uniquely identify each stored procedure in a SQL Server database.  The object_definition function returns the defining T-SQL code for an object based on the object_id value for an object.  The where clause criteria specify the type of stored procedure and the stored procedure’s name.  The T-SQL Definition column is added to a preceding query design for listing stored procedures by their database name, schema name, and object name. 

-- list the T-SQL code for a stored procedure identified by its object_id
select 
 db_name() [database name]
,[schema name] = SCHEMA_NAME([schema_id])
,name [stored proc name]
,object_definition (object_id) [T-SQL Definition]
FROM sys.procedures
where type_desc = N'SQL_STORED_PROCEDURE'
and name = 'uspMyFourthStoredProcedure'

Here’s the results set from the preceding script.  It is for a stored procedure named uspMyFourthStoredProcedure.  The code for defining the stored procedure appears in the column named T-SQL Definition.  You can specify the code for a SQL Server stored procedure with either a create proc or an alter proc statement.  However, the object_definition function always returns the code with the create proc statement.

query results

The next script shows the code for returning the T-SQL for all stored procedures in a database from an information_schema.routines view.  A commented line of code shows how to restrict the result set to a single row based on the name for a specific stored procedure.  The information_schema.routines view includes a column named routine_definition that contains the SQL for defining a stored procedure.  Therefore, this approach is more straightforward than with the custom SQL Server approach that requires an invocation of the object_definition function for the stored procedure object identified by its object_id.

-- list the code in all stored procedures from information_schema.routines 
-- commented code shows syntax for getting T-SQL for a specific stored procedure
select 
 specific_catalog [database name]
,specific_schema [schema name]
,specific_name [stored proc name]
,routine_definition [T-SQL Definition] 
from CodeModuleTypes.information_schema.routines 
where routine_type = 'procedure'
--and specific_name = 'uspMyFourthStoredProcedure'
order by specific_name

The following screen shot displays a segment of the results set from the preceding script.

  • It displays one row for each of the four stored procedures in CodeModuleTypes database.
  • The T-SQL Definition column is cropped so that it is wide enough to display the complete defining T-SQL for the stored procedure named uspMyFourthStoredProcedure.  The other stored procedures have more text so their code does not completely show.
  • However, you can use the Windows right-click copy command to save the full script for the T-SQL Definition column value to the Windows Clipboard.  Next, paste the contents from the Windows Clipboard into a fresh SSMS query tab.
query results

The following screen shot shows a view from the Editor tab of the defining T-SQL code copied from the preceding screen shot for the stored procedure named uspMySecondStoredProcedure.  As you can see, SQL Server’s SSMS automatically reformats the copied stored procedure’s text with carriage returns and line feeds so that it is easy to read.  By the way, this stored procedure can be used to assist in populating the soh_col_subset table from the CodeModuleTypes database.  The Next Steps section of this tip starts with a reference for the url that points at a tip that demonstrates how to use uspMySecondStoredProcedure to create and populate the soh_col_subset table in the CodeModuleTypes database.

stored procedure code

Listing the create and last modified datetimes for a SQL Server Stored Procedure

Two aspects of a stored procedure that you can track are when it was initially created and when it was last modified.  You create a stored procedure with a create proc statement.  You modify a previously created stored procedure with an alter proc statement; this statement does not change the create_date column value in the sys.objects view, but it does freshly populate the modify_date column value.  The following script shows how to query the sys.objects view to derive datetime values for when a stored procedure was initially created and when it was last modified.

  • The script starts by creating a fresh copy of uspMyFifthStoredProcedure which returns all columns from the Employee table in the AdventureWorks database.
  • After the create proc statement, the script invokes the waitfor delay function to pause for one minute.
  • Then, it alters the initial version of uspMyFifthStoredProcedure to select just a subset of the columns from the Employee table.  Additionally, the altered stored procedure joins the Employee table to the Person table to display FirstName and LastName column values for employees.
  • Finally, the script queries the sys.objects view to extract the create_date column value for a stored procedure.  The final query also displays the modify_date column value for a stored procedure in sys.objects.  Although the create_date and modify_date column names indicate a date data type, the values return with a datetime data type.
  • Two where clause criteria restrict the results set.
    • The type value of ‘p’ restricts the results to objects that are T-SQL stored procedures.
    • The name criterion value designates a specific stored procedure for which to return create_date and modify_date values from the sys.objects views.
-- demonstrates ability to show create and last modified datetimes
-- for a stored procedure named uspMyFifthStoredProcedure
 
-- conditionally drop a stored proc
if object_id('dbo.uspMyFifthStoredProcedure') is not null 
     drop proc dbo.uspMyFifthStoredProcedure
go
 
-- create a new stored proc
create proc dbo.uspMyFifthStoredProcedure
as
select * from AdventureWorks2014.HumanResources.Employee
go
 
-- wait a minute
WAITFOR DELAY '00:01:00.000'
go
 
-- alter a stored proc
alter proc dbo.uspMyFifthStoredProcedure
as
select
 Employee.BusinessEntityID
,Person.FirstName
,Person.LastName
,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person
on Employee.BusinessEntityID = Person.BusinessEntityID
go
 
-- display create datetime and last modified datetime
-- for uspMyFifthStoredProcedure's full name (db_name, schema_name, name)
select 
 [database name] = db_name() 
,[schema name] =  SCHEMA_NAME([schema_id])
,name [stored proc name]
,create_date [create date]
,modify_date [last modify date]
from sys.objects
where type = 'P'
and name = 'uspMyFifthStoredProcedure'

Here’s the results set from the preceding script.

  • The stored proc name column corresponds to the name criterion in the select statement for the sys.objects view.
  • In addition to the name for the stored procedure’s object, uspMyFifthStoredProcedure is identified by its database name from the db_name function and its schema name from the schema_name function.
  • There is about one minute of elapsed time between the modify_date and create_date column values.  This gap corresponds to the waitfor delay function argument plus the execution time for the alter proc statement.
query results

When was the last time a SQL Server Stored Procedure Executed?

The script below executes the altered version of the uspMyFifthStoredProcedure stored procedure from the preceding example twice.  After each time the stored procedure is run, the script queries an inner join of the sys.dm_exec_procedure_stats dynamic management view and the sys.objects catalog view.  Between the first and second invocation of uspMyFifthStoredProcedure, a waitfor delay function pauses the code’s execution for one minute.

-- Show when was the last time a stored procedure 
-- named uspMyFourthStoredProcedure was run
 
-- run a stored proc
exec uspMyFifthStoredProcedure
 
-- when did it last run
select
 db_name() [database name]
,[schema name] = SCHEMA_NAME([schema_id])
,o.name
,ps.last_execution_time 
from   sys.dm_exec_procedure_stats ps 
inner join
       sys.objects o 
       ON ps.object_id = o.object_id 
where o.type = 'P'
and o.name = 'uspMyFifthStoredProcedure'
order by
       ps.last_execution_time
 
-- wait a minute
waitfor delay '00:01:00.000'
 
-- run a stored proc
exec uspMyFifthStoredProcedure
 
-- when did it last run
select
 db_name() [database name]
,[schema name] = SCHEMA_NAME([schema_id])
,o.name
,ps.last_execution_time 
from   sys.dm_exec_procedure_stats ps 
inner join
       sys.objects o 
       ON ps.object_id = o.object_id 
where o.type = 'P'
--and o.schema_id = schema_name(schema_id)
and o.name = 'uspMyFifthStoredProcedure'
order by
       ps.last_execution_time

Here’s an excerpt from the output for the preceding script.  The excerpt contains four panes.

  • The first pane shows just the first three rows from the results set of initially running uspMyFifthStoredProcedure.
  • The second pane shows the value of the last_execution_time column value from the sys.dm_exec_procedure_stats dynamic management view after the initial run of uspMyFifthStoredProcedure.  As you can see, the last_execution_time column value is returned with a datetime datatype. 
  • The third pane shows just the first three rows from the results set of running uspMyFifthStoredProcedure a second time.
  • The fourth pane shows the last_execution_time from the sys.dm_exec_procedure_stats dynamic management view after the second run of uspMyFifthStoredProcedure.
  • Notice that the difference in last execution times between the second and fourth panes is about one minute.  This value reflects the value submitted to the waitfor delay function between the first and second runs of uspMyFifthStoredProcedure plus the time for running the stored procedure a second time.
query results
Next Steps
  • You can try out the code examples for this tip with the database created in the “Storing Result Sets from a Stored Procedure” tip.  In particular, use the first code segment in the “Saving the results set from a select statement within a stored procedure to a regular table” section to create and freshly populate the soh_col_subset table in the CodeModuleTypes database.
  • Next, copy the script(s) from this tip that you want to test and modify.  Examine the output from the scripts you select to verify the code performs as described.
  • Finally, modify the script(s) to reference another source database with selected stored procedures and tables that follow from your requirements.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rick Dobson Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, October 23, 2019 - 11:44:41 AM - Daniel Bragg Back To Top (82876)

Thank you so much for the thoughts and extra digging.  While dm_exec_procedure_stats won't help with detecting unused procedures, there is still lots of goodness here, and I'll find uses for it, for sure.  I dug through some of the provided links (I greatly appreciate most of what Brent Ozar writes, when it's not too deep for my pay grade) and appreciate them all.  I am a developer, so I agree that SQL can be a tool or a programming language, or even an entire development environemnt (you can do a lot and never leave Management Studio).

Tuesday, October 22, 2019 - 11:31:11 AM - Rick Dobson Back To Top (82862)

Hey Daniel,

I tend to think about SQL Server as a T-SQL programming operation.  You can do a lot in SQL Server with T-SQL, but sometimes you need to get down to SQL Server internals, such as memory management, memory grants, and even reconfigurations that can impact memory use and query cache contents.  I lump these together as administrative topics.  While they are mightily important, they just do not appeal to me personally.  However, I did some research to help point you to learning more about plan caches and their interaction with the dm_exec_procedure_stats system management view.  I hope this research is enough to get you started understanding the issues that you need to know to address the topics that require answers for your work.

dm_exec_procedure_stats and cached stored procedures in sql server

The dm_exec_procedure_stats system management view returns aggregate performance statistics for cached stored procedures (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql?view=sql-server-ver15).  The view has one row for each cached stored procedure.  One of the performance statistics is last_execution_time, which tracks the last start datetime when a stored procedure was invoked.

While a developer can choose to remove one or all cached stored procedure plans, SQL Server can remove plans for other reasons besides explicit developer actions.

This reference (https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/) illustrates eight different ways that a developer can clear the plan cache.  Reconfiguring SQL Server can also cause changes in the contents of the query cache.  This reference (https://mattsql.wordpress.com/2012/06/25/dont-believe-everything-you-read-reconfigure-flushes-the-plan-cache/) identifies at least a dozen types of SQL Server reconfiguration actions that can affect the contents of the query cache.

SQL Server reserves a certain amount of memory for use as a data buffer and for caching query plans.  As “memory pressure” increases on SQL Server, query plans can be removed from memory.  Memory pressure can come from SQL Server or even Windows.  Here’s a reference to this general topic (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms181055(v=sql.105)?redirectedfrom=MSDN).

Brent Ozar has a pretty cool article (https://www.brentozar.com/archive/2016/08/memory-grants-week-introduction-query-memory/) on memory grants, query memory, and query performance.  This is related to related via the topic of memory to memory caches.  He also points out in his article how Microsoft has been tweaking query performance across SQL Server versions 2012, 2014, and 2016.

Have fun Daniel pursuing these topics.  I hope at least one reference gives your the answers you seek or points you to an additional reference with the answers you seek.

Cheers,

Rick Dobson

Friday, October 18, 2019 - 6:39:59 AM - W.E. Back To Top (82818)

Hello there,
the use of dm_exec_procedure_stats  to find the last execution date will just not work. As already mentioned in comments here, this view contains a procedure just as long as it is in the cache. Removing from the cache can happen any time. Even worse: a procedure created with the recompile hint does not even get into the cache.

Another note to this: a procedure can have several execution plans in the procedure cache, leading to multiple rows in dm_exec_procedure_stats  for the same object. This means you would at least need a max() to get a "right" value.

Beside of these things, really nice article.

Thursday, October 17, 2019 - 4:04:55 PM - Rick Dobson Back To Top (82812)

Hey Daniel,

Here's another thought that I wanted to share with you.

I have not done any testing, but it seems to me that when the create date and last-run date equal one another, then the stored proc has not been run since the date it was initially created.  This may be something you care to pursue to answer your question.

Best regards,

Rick Dobson

Thursday, October 17, 2019 - 3:59:17 PM - Rick Dobson Back To Top (82811)

Hi Daniel,

I am glad you were motivated to try code from the tip.

The sys.dm_exec_query_stats view returns aggregate performance statistics for CACHED STORED PROCEDURES. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.  You can add a stored procedure to the cache as simply as invoking it.

I hope this reply helps you.

Rick Dobson

Wednesday, October 16, 2019 - 12:16:05 PM - Daniel Bragg Back To Top (82802)

What process(es) will cause dm_exec_procedure_stats to flush/reset its content?  I built a script that I expected would tell me which procedures have never run (because there is no data present in dm_exec_procedure_stats) and I found lots of procedures that I know should be in there, although none of them would have been run recently.














get free sql tips
agree to terms