Problem
I was recently tasked with tracking and monitoring user-defined functions in the databases that I administer. I noticed another MSSQLTips.com tip covers this topic for stored procedures, but the same code does not apply to user-defined functions. Please provide code samples that enumerate the user-defined functions in a 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 user-defined functions as well as when they last ran.
Solution
A user-defined function (udf) 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 both user-defined functions and stored procedures. This tip focuses on how to use selected views that can facilitate monitoring and tracking user-defined functions. See this prior tip for examples of how to track and monitor stored procedures in a database.
In this tip, you will learn
- a couple of different ways to enumerate the user-defined functions in a database by schema
- to display the type of user-defined functions in a database; the type denotes a udf as scalar-valued, inline table-valued, or multi-statement table-valued
- to display the T-SQL that defines what a udf does
- to retrieve the initial creation date and the last date when a udf was modified
- to show when a cached udf 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 user-defined functions. MSSQLTips.com also offers a tutorial on dynamic management views.
List User Defined Functions in a SQL Server Database
A way to get started enumerating the user-defined functions in a database is with sys.objects, which is a catalog view. The sys.objects catalog view provides a SQL Server specific way of querying information about metadata, such as a udf name and the name of the schema in which it resides. It is also relevant to recall that user-defined functions reside in a database. Therefore, a udf within a SQL Server instance is uniquely identified by the combination of database name, schema name, and object name. While you are enumerating the user-defined functions in a database, you will likely want to identify the type of each udf. Sys.objects facilitates this goal as well.
The following script demonstrates one approach for enumerating the names of user-defined functions 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.objects. This view can return a row for each object, such as a udf, in a database. Five 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 udf’s row in sys.objects. Starting with SQL Server 2005, user-defined functions with the same name can reside in a database within different schemas.
- The name column value from sys.objects returns the name of a udf within a schema within a database.
- The type and type_desc columns characterize the type of udf on a row in sys.objects.
- The type column denotes the type of udf with an abbreviated code; the code values of a udf for a T-SQL developer are FN, IF, or TF.
- The type_desc column is the name for the function type:
- SQL_SCALAR_FUNCTION for the FN type code
- SQL_INLINE_TABLE_VALUED_FUNCTION for the IF type code; this is a udf that returns a rowset in a table object
- SQL_TABLE_VALUED_FUNCTION for the TF type code; this is a udf that returns a rowset in a table variable (as opposed to a table object)
- The where clause restricts output to typical user-defined functions coded in T-SQL — namely, those with a type of FN, IF, or TF.
- The order by clause arranges the results set rows in order by type code values.
- Another pair of function type values include: FS for a clr scalar-function and FT for a clr table-valued function. These two udf types are outside the scope to this tip which focuses exclusively on user-defined functions for T-SQL developers.
use CodeModuleTypes
go
-- list user-defined functions with database name and schema name
-- from sys.objects
SELECT
db_name() [database name]
,[schema name] = SCHEMA_NAME([schema_id])
,name [udf name]
,type [type code]
,type_desc [type description]
from sys.objects
-- for one of three udf types
where type IN('FN', 'IF', 'TF')
-- orders alphabetically from scalar to inline to multi-statement
order by [type code]
Here’s the output from the preceding script.
- Database name for all stored procedures is CodeModuleTypes because of the use statement.
- For the user-defined functions in this tip, the schema name is always 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 udf name column is from sys.objects.
- The type code column shows the type value from sys.objects.
- The type description column displays the type_desc column values from sys.objects.
Another approach to enumerating user-defined functions in a database is via information_schema views. These views are not specific to SQL Server (for example, 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 udf resides.
- Specific_name returns the name of the stored procedure object.
- Additionally, a where clause designates ‘function’ as the type of routine to be listed. A routine_type value of function enables the selection of user-defined functions. In contrast, a routine_type value of procedure enables the selection of stored procedures.
-- list udfs with catalog/database name, schema name, udf name -- from information_schema.routines select specific_catalog [database name] ,specific_schema [schema name] ,specific_name [udf name] ,routine_type from information_schema.routines where routine_type = 'function' order by [udf name]
I did not discover a column in information_schema.routines for revealing the type of udf. I was able to devise a hack that got me part of the way to revealing udf type, but the view was clearly not designed with the goal of displaying a udf type in a straightforward way. If you need user-defined functions classified by type consider using sys.objects instead of information_schmea.routines.
Here’s a display with the results set for the information_schema.routines output enumerating user-defined functions. Notice that the results in the schema name and udf name columns exactly match those from sys.objects.
Display the SQL Server User Defined Function Code
After you get a list of all the user-defined functions in a database, you may care to examine the defining code for a subset of them. By defining code, I mean the create function statement that can generate a fresh version of a udf.
Here’s a simple script to list all the user-defined functions in a database along with the defining code for each udf. The script draws on sys.objects and the object_definition function. The prior section confirmed that you can enumerate all user-defined functions in a database with sys.objects and a filter for type based on the three types of user-defined functions (FN, IF, and TF) for T-SQL developers. The object_definition system function returns the defining T-SQL code as text for code modules, such as user-defined functions. This function operates on the object_id identifier for a udf in the current database, which is CodeModuleTypes in this tip. Because each udf has its own object_id value in a database, the script can return the text for each udf in a database. If you include a where clause criterion for which subset of user-defined functions to return code, then the script returns in its T-SQL Definition column create function statements for some instead of all user-defined functions for T-SQL developers.
-- list the T-SQL code for all udfs 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.objects
-- for one of three udf types
where type IN ('FN', 'IF', 'TF')
The following screen shot displays the output from the preceding script. User-defined functions are identified by their database name, schema name, and udf name. The last column displays an excerpt from the defining code for a udf. The create function term followed by the udf name and parameter names, if they apply, are contained within a red border for each udf.
By right clicking and selecting Copy in any row of the T-SQL Definition column, you can copy the defining T-SQL code for a function into the Windows clipboard. Then, you can copy from the Windows clipboard into an empty query window or other container of your choice, such as a Notepad text file. The following screen shot shows the defining code from the first row in the preceding screen shot.
List Create and Last Modified Datetimes for a SQL Server User Defined Function
Two significant features of a udf that you can track are when it was initially created and when it was last modified.
- You create a udf with a create function statement. Each create function statement freshly populates the create_date column for a udf in sys.objects.
- You modify a previously created udf with an alter function statement; this statement does not change the create_date column value in sys.objects, but it does freshly populate the modify_date column value.
The following script shows how to query sys.objects to derive datetime values for when a udf was initially created and when it was last modified.
- The script starts by creating a fresh copy of a scalar-valued udf named udf_num1_by_num2 which has two parameters (@num1 and @num2). The udf divides @num1 by @num2 and returns the quotient in an int data type. The int data type truncates quotient digits to the right of the decimal point.
- Next, a select statement displays the parameter values and the scalar value returned by the udf.
- After the select statement, the script invokes a waitfor delay statement to pause for one minute.
- A go statement trails the waitfor delay statement so that the next statement starts at the beginning of a batch.
- Then, the script alters the initial version of udf_num1_by_num2 so quotient values are returned via a numeric(38,6) data type.
- Next, a select statement displays the parameter values and the scalar value returned by the altered udf.
- Finally, the script queries sys.objects to extract the create_date column value for the udf. The final query also displays the modify_date column value for the udf in sys.objects. Although the create_date and modify_date column names indicate a date data type, the values return a datetime data type with time specified down to the level of milliseconds.
- Two where clause criteria restrict the results set.
- The type value of ‘FN’ restricts the results to objects that are T-SQL scalar-valued user-defined functions.
- The name criterion value designates a specific udf for which to return create_date and modify_date values from sys.objects.
-- conditionally drop a udf with a try...catch code block -- that works for all three major T-SQL udf types begin try drop function [dbo].[udf_num1_by_num2] end try begin catch select error_number() [Error Number] ,error_message() [Error Message] end catch go -- create function scalar-valued udf -- notice returns type is int create function [dbo].[udf_num1_by_num2] (@num1 int, @num2 float) returns int as begin return (@num1/@num2) end go -- real quotient is .5, but truncated quotient for int data type is 0 select num1 = 1, num2 = 2, [dbo].[udf_num1_by_num2](1,2) [quotient as int] -- wait a minute WAITFOR DELAY '00:01:00.000' go -- alter function scalar-valued udf -- notice returns type is modified to numeric(38,6) alter function [dbo].[udf_num1_by_num2] (@num1 int, @num2 float) returns numeric(38,6) as begin return (@num1/@num2) end go -- real quotient is .5, but truncated quotient is 0 select num1 = 1, num2 = 2, [dbo].[udf_num1_by_num2](1,2) [quotient as numeric(38,6)] -- display create datetime and last modified datetime -- for [dbo].[udf_num1_by_num2] (@num1 int, @num2 float) select [database name] = db_name() ,[schema name] = SCHEMA_NAME([schema_id]) ,name [scalar-valued udf name] ,create_date [create date] ,modify_date [last modify date] from sys.objects where type = 'FN' and name = 'udf_num1_by_num2'
Here’s the results set from the preceding script.
- The first pane shows the results set from the first select statement for the initial version of the udf_num1_by_num2 function. This pane reveals truncation with value of 0 because of the int data type for the udf output.
- The second pane shows the results set from the select statement for the altered version of the udf_num1_by_num2 function; the quotient in this case is 0.500000. This pane is more resistant to truncation because output values are represented through six places after the decimal point.
- The third pane shows the create date and last modified date for udf_num1_by_num2. Because of the waitfor delay statement between the create function and alter function statements, the last modified date is about one minute later than the create date.
When did a SQL Server User Defined Function last run?
This tip’s approach to determine when a udf last ran relies on the fact that you invoke a udf from within a query batch. Therefore, the solution to assessing when a udf last ran depends on determining when the batch that invokes the udf last ran.
Query batches can be delimited with preceding and trailing go statements. This is convenient for assessing when a query batch last ran because you can place the query statement for invoking a udf in a batch by itself. Then, you can determine from the sys.dm_exec_query_stats dynamic management view the datetime that the query batch for invoking a udf last ran through the view’s last_execution_time column value. By merging sys.dm_exec_query_stats output with sys.dm_exec_sql_text output, you can verify the query statement within a batch for which a last execution time is retrieved.
The following script, that is designed to run from the CodeModuleTypes database context, demonstrates the application of these guidelines for two separate instances of a select statement. The two separate invocations of udf_num1_by_num2 are separated by a delay of one minute.
- Notice that the first execution of udf_num1_by_num2(1,2) is in a select statement delimited by preceding and trailing go statements. These go statements define the beginning and end points of a T-SQL batch.
- After the first execution of the udf, the run-time for the first execution is derived and displayed by merging output from sys.dm_exec_query_stats and sys.dm_exec_sql_text.
- A cross apply operator implements the merge by matching the outputs from the two dynamic views through their sql_handle values, which point to corresponding rows from both views.
- The where clause specifies the start of the text for the T-SQL statement that invokes the udf.
- Next, a waitfor delay statement causes a one-minute pause in the session.
- Then, a second execution of udf_num1_by_num2(1,2) is invoked within a select statement in another batch delimited by go statements.
- Finally, output from the sys.dm_exec_query_stats and sys.dm_exec_sql_text dynamic views are merged by sql_handle values with a where clause for the text of the statement invoking the udf. This code displays the second execution time for the udf.
-- display run-times demonstration for a udf -- first execution of udf go select num1 = 1, num2 = 2, dbo.udf_num1_by_num2(1,2) [quotient as numeric(38,6)] go -- display udf first run-time select qs.last_execution_time, qt.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt where qt.text like 'select num1 = 1, num2 = 2, dbo.udf_num1_by_num2(1,2)%' -- wait a minute waitfor delay '00:01:00.000' -- second execution of udf go select num1 = 1, num2 = 2, dbo.udf_num1_by_num2(1,2) [quotient as numeric(38,6)] go -- display udf second run-time select qs.last_execution_time, qt.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt where qt.text like 'select num1 = 1, num2 = 2, dbo.udf_num1_by_num2(1,2)%'
Here’s a screen shot with the output from the preceding script. The Results tab has four panes.
- The first pane shows the output from the first run of udf_num1_by_num2 with parameter values of 1 and 2 for @num1 and @num2, respectively.
- The second pane shows the start time for the first execution of udf_num1_by_num2; the time is 4 PM, 4 minutes, 59 seconds, and .2 milliseconds.
- The third pane shows the output from the second run of udf_num1_by_num2 with parameter values of 1 and 2 for @num1 and @num2, respectively.
- The fourth pane shows the start time for the second execution of udf_num1_by_num2; the time is 4 PM, 5 minutes, 59 seconds, and .2 milliseconds.
- Notice that the start times for the first and second runs of udf_num1_by_num2 differ by one minute, which matches the value specified in the waitfor delay statement.
Next Steps
- This tip demonstrates how to track and monitor user-defined functions previously created in a database named CodeModuleTypes from this prior tip. You can use the example T-SQL scripts from the prior tip to create the user-defined functions tracked in this tip. The examples explicitly target the CodeModuleTypes database, but you can use any other database context that you prefer.
- After confirming you have the user-defined functions from the prior tip, 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 user-defined functions that follow from your requirements.
