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.