View user-defined functions

Article
11/18/2022

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

You can gain information about the definition or properties of a user-defined function in SQL Server by using SQL Server Management Studio or Transact-SQL. You may need to see the definition of the function to understand how its data is derived from the source tables or to see the data defined by the function.

If you change the name of an object referenced by a function, you must modify that function so that its text reflects the new name. Therefore, before renaming an object, display the dependencies of the object first to determine if any functions are affected by the proposed change.

Permissions

Using sys.sql_expression_dependencies to find all the dependencies on a function requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. System object definitions, like the ones returned in OBJECT_DEFINITION, are publicly visible.

Use SQL Server Management Studio

Show a user-defined function's properties

In Object Explorer, select the plus sign next to the database that contains the function to which you want to view the properties, and then select the plus sign to expand the Programmability folder.
Select the plus sign to expand the Functions folder.
Select the plus sign to expand the folder that contains the function to which you want to view the properties:
- Table-valued Function
- Scalar-valued Function
- Aggregate Function

Right-click the function of which you want to view the properties and select Properties.

The following properties appear in the Function Properties - function_name dialog box.

Function name	Description
Database	The name of the database containing this function.
Server	The name of the current server instance.
User	The name of the user of this connection.
Created date	Displays the date the function was created.
Execute As	Execution context for the function.
Name	The name of the current function.
Schema	Displays the schema that owns the function.
System object	Indicates whether the function is a system object. Values are `True` and `False`.
ANSI NULLs	Indicates if the object was created with the ANSI NULLs option.
Encrypted	Indicates whether the function is encrypted. Values are `True` and `False`.
Function Type	The type of user defined function.
Quoted identifier	Indicates if the object was created with the quoted identifier option.
Schema bound	Indicates whether the function is schema-bound. Values are True and False. For information about schema-bound functions, see the SCHEMABINDING section of CREATE FUNCTION (Transact-SQL).

Use Transact-SQL

Get the definition and properties of a function

In Object Explorer, connect to an instance of the Database Engine.
On the Standard bar, select New Query.

Copy and paste one of the following examples into the query window and select Execute.

The following code sample gets the function name, definition, and relevant properties.

USE AdventureWorks2022;
GO
-- Get the function name, definition, and relevant properties
SELECT sm.object_id,
   OBJECT_NAME(sm.object_id) AS object_name,
   o.type,
   o.type_desc,
   sm.definition,
   sm.uses_ansi_nulls,
   sm.uses_quoted_identifier,
   sm.is_schema_bound,
   sm.execute_as_principal_id
-- using the two system tables sys.sql_modules and sys.objects
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
-- from the function 'dbo.ufnGetProductDealerPrice'
WHERE sm.object_id = OBJECT_ID('dbo.ufnGetProductDealerPrice')
ORDER BY o.type;
GO

The following code sample gets the definition of the example function dbo.ufnGetProductDealerPrice.

USE AdventureWorks2022;
GO
-- Get the definition of the function dbo.ufnGetProductDealerPrice
SELECT OBJECT_DEFINITION (OBJECT_ID('dbo.ufnGetProductDealerPrice')) AS ObjectDefinition;
GO

For more information, see sys.sql_modules (Transact-SQL) and OBJECT_DEFINITION (Transact-SQL).

Get the dependencies of a function

In Object Explorer, connect to an instance of the Database Engine.
On the Standard bar, select New Query.

Copy and paste the following example into the query window and select Execute.

USE AdventureWorks2022;
GO
-- Get all of the dependency information
SELECT OBJECT_NAME(sed.referencing_id) AS referencing_entity_name,
    o.type_desc AS referencing_desciption,
    COALESCE(COL_NAME(sed.referencing_id, sed.referencing_minor_id), '(n/a)') AS referencing_minor_id,
    sed.referencing_class_desc, sed.referenced_class_desc,
    sed.referenced_server_name, sed.referenced_database_name, sed.referenced_schema_name,
    sed.referenced_entity_name,
    COALESCE(COL_NAME(sed.referenced_id, sed.referenced_minor_id), '(n/a)') AS referenced_column_name,
    sed.is_caller_dependent, sed.is_ambiguous
-- from the two system tables sys.sql_expression_dependencies and sys.object
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
-- on the function dbo.ufnGetProductDealerPrice
WHERE sed.referencing_id = OBJECT_ID('dbo.ufnGetProductDealerPrice');
GO

For more information, see sys.sql_expression_dependencies (Transact-SQL) and sys.objects (Transact-SQL).

Share via