This article presents two ways to return a list of user-defined functions in a SQL Server database.

Option 1 – The ROUTINES Information Schema View

You can use the ROUTINES information schema view to get a list of all user-defined functions in a database.

This view returns stored procedures as well as functions, so you’ll need to add a WHERE clause to narrow it down to just functions.

USE Music;
SELECT 
  ROUTINE_SCHEMA,
  ROUTINE_NAME,
  ROUTINE_TYPE,
  DATA_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION';

Result:

+------------------+-------------------------+----------------+-------------+
| ROUTINE_SCHEMA   | ROUTINE_NAME            | ROUTINE_TYPE   | DATA_TYPE   |
|------------------+-------------------------+----------------+-------------|
| dbo              | ISOweek                 | FUNCTION       | int         |
| dbo              | ufn_AlbumsByGenre       | FUNCTION       | TABLE       |
| dbo              | ufn_AlbumsByArtist      | FUNCTION       | TABLE       |
| dbo              | ufn_AlbumsByGenre_MSTVF | FUNCTION       | TABLE       |
+------------------+-------------------------+----------------+-------------+

Return The Function’s Definition

This view also has a ROUTINE_DEFINITION column, so you can easily return each function’s definition if required.

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION';

Option 2 – The sys.objects System Catalog View

Another way to return a list of functions is to query the sys.objects system catalog view.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name,
  type_desc
FROM sys.objects
WHERE type IN ('AF','FN','FS','FT','IF','TF');

Result:

+----------+-------------------------+----------------------------------+
| Schema   | name                    | type_desc                        |
|----------+-------------------------+----------------------------------|
| dbo      | ISOweek                 | SQL_SCALAR_FUNCTION              |
| dbo      | ufn_AlbumsByGenre       | SQL_INLINE_TABLE_VALUED_FUNCTION |
| dbo      | ufn_AlbumsByArtist      | SQL_INLINE_TABLE_VALUED_FUNCTION |
| dbo      | ufn_AlbumsByGenre_MSTVF | SQL_TABLE_VALUED_FUNCTION        |
+----------+-------------------------+----------------------------------+

Here I’ve explicitly included all function types in the WHERE clause.

If you’re running an ad hoc query but you can’t remember all the types, you could do something like this:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name,
  type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION';

Or this:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name,
  type_desc
FROM sys.objects
WHERE RIGHT(type_desc, 8) = 'FUNCTION';

Return The Function’s Definition

The sys.objects view doesn’t include a column for the object’s definition. If you want to return each function’s definition, you can join it with the sys.sql_modules system view.

Example:

SELECT definition
FROM sys.objects o
INNER JOIN sys.sql_modules m 
ON o.object_id = m.object_id
WHERE type IN ('AF','FN','FS','FT','IF','TF');