Bagikan melalui

  • Artikel

Viewing User-Defined Functions

Several system stored procedures and catalog views provide information about stored procedures. Using these, you can:

  • See the definition of the function. That is, the Transact-SQL statements used to create a user-defined function. This can be useful if you do not have the Transact-SQL script files used to create the function.

  • Get information about a function such as its schema, when it was created, and its parameters.

  • List the objects used by the specified function, and the objects that use the specified function. This information can be used to identify the functions affected by the changing or removal of an object in the database.

To view the definition of a user-defined function

To view information about a user-defined function

To view the dependencies of a user-defined function

Examples

A. Using system catalog views to return user-defined function information

The following examples use the catalog views sys.objects and sys.parameters to return information about user-defined functions and their parameters.

-- Display metadata about the user-defined functions in AdventureWorks.
USE AdventureWorks;
GO
SELECT *
FROM sys.objects
WHERE type IN ('IF','TF','FN','FS','FT');
GO
-- Return parameters associated with the functions
SELECT o.name AS FunctionName, p.*
FROM sys.objects AS o
JOIN sys.parameters AS p ON o.object_id = p.object_ID
WHERE type IN ('IF','TF','FN','FS','FT');
GO

B. Using OBJECT_DEFINITION

The following example uses the system function OBJECT_DEFINITION to return the definition of user-defined function dbo.ufnGetContactInformation.

USE AdventureWorks;
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ufnGetContactInformation'));
GO

C. Using sys.sql_expression_dependencies

The following example uses the sys.sql_expression_dependencies and sys.columns catalog views to return the table and column names on which the user-defined function dbo.ufnGetContactInformation depends.

USE AdventureWorks;
GO
SELECT OBJECT_NAME(d.referencing_id) AS referencing_entity, 
    OBJECT_NAME(referenced_id) AS referenced_entity, 
    referenced_minor_id AS referenced_column_id,
    c.name AS referenced_column 
FROM sys.sql_expression_dependencies AS d
JOIN sys.columns AS c ON c.object_id = d.referenced_id
    AND c.column_id = d.referenced_minor_id
WHERE d.referencing_id = OBJECT_ID(N'AdventureWorks.dbo.ufnGetContactInformation');