2 Ways to List All Table-Valued Functions in a SQL Server Database

This article presents two ways to return a list of table-valued 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 table-valued functions in a database.

This view returns one row for each stored procedure and function that can be accessed by the current user in the current database. This could include routines that aren’t table-valued functions, so you’ll need to add a WHERE clause to narrow it down to just table-valued functions.

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

Result:

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

In this case, the Music database contains three table-valued functions.

The ROUTINE_TYPE column returns PROCEDURE if it’s a stored procedure and FUNCTION if it’s a function. The DATA_TYPE column returns TABLE only if it’s a table-valued function. Therefore, I could’ve omitted the ROUTINE_TYPE column from the WHERE clause, but I included it anyway.

Return The Function’s Definition

This view also has a ROUTINE_DEFINITION column that contains the definition. Here’s an example of modifying the above query to return the definition for just one routine:

SELECT TOP(1) ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
AND DATA_TYPE = 'TABLE';

Result:

+----------------------+
| ROUTINE_DEFINITION   |
|----------------------|
| 
CREATE FUNCTION [dbo].[ufn_AlbumsByGenre](@GenreId int)
RETURNS TABLE
AS
RETURN(  
    SELECT 
      ar.ArtistName,
      al.AlbumName,
      g.Genre
    FROM Genres g 
      INNER JOIN Albums al
        ON g.GenreId = al.GenreId 
      INNER JOIN Artists ar 
        ON al.ArtistId = ar.ArtistId
    WHERE g.GenreId = @GenreId
);                      |
+----------------------+

In this case I used the TOP() clause to limit the results to just one row, but you could easily list the definition of all functions by removing the TOP() clause.

Option 2 – The sys.objects System Catalog View

Another way to return a list of table-valued 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 ('IF', 'TF', 'FT');

Result:

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

Return The Function’s Definition

You can join this with the sys.sql_modules view if you want the definition to be returned.

Example:

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

Result:

+--------------+
| definition   |
|--------------|
| 
CREATE FUNCTION [dbo].[ufn_AlbumsByGenre](@GenreId int)
RETURNS TABLE
AS
RETURN(  
    SELECT 
      ar.ArtistName,
      al.AlbumName,
      g.Genre
    FROM Genres g 
      INNER JOIN Albums al
        ON g.GenreId = al.GenreId 
      INNER JOIN Artists ar 
        ON al.ArtistId = ar.ArtistId
    WHERE g.GenreId = @GenreId
);              |
+--------------+

Again, this uses the TOP() clause to limit the results to just one row.