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.
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.
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.
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.
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.
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.
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.
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.
