Search And Find All User Defined Functions / UDF In SQL Server

In several situations, I wanted to find all user defined functions in SQL Server database along with the details like function type, created date, etc.. . I’ve done these things using simple Transact-SQL statements and also by using the SQL Server Management Studio (SSMS) object explorer GUI.

In my earlier articles, I’ve explained the various techniques to find the stored procedures as well as the methods to find tables in the database. Using similar techniques, here I’ve listed down various methods to search and find all user defined functions in a database.

Find All User Defined Functions Using Transact-SQL

To get the list of all the functions in a database, you can use the transact SQL statement against the system objects like Sys.Objects, Information_Schema.Routines, syscomments or Sys.Sql_Modules.

1. Using Sys.objects

From sys.objects system view, you can get the name, schema id, UDF type, created date time, altered date time and other details. you can used sys.object view to get the list of user defined functions in 2 ways. One way is to use the type column to filter by object types. Another way is to use the type_desc, i.e. the description of the object type to filter and get only the UDFs. Here I’ve provided the sql statements for both the methods to find all user defined fnctions.

USE WideWorldImporters;
GO
SELECT 
     name AS 'Function Name',
     SCHEMA_NAME(schema_id) AS 'Schema',
     type_desc AS 'Function Type', 
     create_date AS 'Created Date'
FROM 
     sys.objects
WHERE 
     type in ('FN', 'IF', 'FN', 'AF', 'FS', 'FT');
GO

In the above statement the object types of functions are:

• FN = SQL scalar function
• IF = SQL inline table valued function
• TF = SQL table valued function
• AF = CLR aggregate function
• FS = CLR scalar function
• FT = CLR table valued function

Alternatively, you can use the below query with type_desc in where clause instead of using the type column. This is an ugly way of looking for UDF, but works.

USE WideWorldImporters;
GO
SELECT 
     name AS 'Function Name',
     SCHEMA_NAME(schema_id) AS 'Schema',
     type_desc AS 'Function Type', 
     create_date AS 'Created Date'
FROM 
     sys.objects
WHERE 
     type_desc LIKE '%FUNCTION%';
GO

2. Using Information_Schema.Routines

You can use the Information_Schema.Routines system view to list down all the functions in a database. From this view you can get the name, definition / source code, database schema, created and modified date, and more details of the UDF. Here is the simple query to find all the user defined functions in a database.

USE WideWorldImporters;
GO
SELECT
    ROUTINE_NAME, 
    ROUTINE_DEFINITION , 
    ROUTINE_SCHEMA, 
	DATA_TYPE,
	CREATED
FROM
    INFORMATION_SCHEMA.ROUTINES 
WHERE
	ROUTINE_TYPE = 'FUNCTION'
GO

3. Using Syscomments

You can use the syscomments system view to list the object id and the source code of any programmable objects within the database. Here is a query using syscomments to find all the functions in a database. If needed you can use OBJECT_SCHEMA_NAME(id) in the select, to get the schema name of the UDF.

USE WideWorldImporters;
GO
SELECT
	DISTINCT OBJECT_NAME(id)
FROM
	syscomments
WHERE 
	OBJECTPROPERTY(id, 'IsInlineFunction') = 1 OR
	OBJECTPROPERTY(id, 'IsScalarFunction') = 1 OR
	OBJECTPROPERTY(id, 'IsTableFunction') = 1
GO

4. Using Sys.sql_modules

Just like syscomments, you can use sys.sql_modules system catalog view to list all the user defined functions. Here is the transact statement.

USE WideWorldImporters;
GO
SELECT
	DISTINCT OBJECT_NAME(object_id) AS 'Function Name', 
	OBJECT_SCHEMA_NAME(object_id) AS 'Schema Name'
FROM
	sys.sql_modules
WHERE 
	OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1 OR
	OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 OR
	OBJECTPROPERTY(object_id, 'IsTableFunction') = 1
GO

Find All UDFs Using SSMS Object Explorer GUI

Another easiest way to find all the user defined function is through the SQL Server Management Studio (SSMS) Object Explorer window. In case if you are not familiar with this method, just follow the below steps.

1. In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
2. Expand the Programmability folder.
3. Expand the Functions folder. Under the function folder, you can find sub folders for each type of UDF. Expand the sub folders to list all the user defined functions.

Please write a comment and let me know your suggestions.

Reference

• About user defined functions in MSDN.