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

• sys.sql_modules (Transact-SQL)
• OBJECT_DEFINITION (Transact-SQL)
• sp_helptext (Transact-SQL)

To view information about a user-defined function

• sys.objects (Transact-SQL)
• sys.parameters (Transact-SQL)
• sp_help (Transact-SQL)

To view the dependencies of a user-defined function

• sys.sql_dependencies (Transact-SQL)
• sp_depends (Transact-SQL)

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_dependencies

The following example uses the sys.sql_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 d.class, OBJECT_NAME(d.object_id) AS ObjectName, 
    OBJECT_NAME(referenced_major_id) AS ReferencedObjectName, 
    referenced_minor_id AS ReferencedColumnID,
    c.name AS ReferencedColumnName,
    is_selected, is_updated, is_select_all 
FROM sys.sql_dependencies AS d
JOIN sys.columns AS c ON c.object_id = d.referenced_major_id
    AND c.column_id = d.referenced_minor_id
WHERE d.object_id = OBJECT_ID(N'AdventureWorks.dbo.ufnGetContactInformation');
GO

See Also

Other Resources

Implementing User-defined Functions

Help and Information

Getting SQL Server 2005 Assistance

Change History