In SQL Server, you can use the Transact-SQL ROUTINE_COLUMNS system information schema view to find the columns returned by a table-valued function.

More specifically, it returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.

To use this view, specify the fully qualified name of INFORMATION_SCHEMA.ROUTINE_COLUMNS.

Example 1 – Basic Usage

Here’s an example.

SELECT 
  TABLE_CATALOG AS [Database],
  TABLE_SCHEMA AS [Schema],
  TABLE_NAME AS [Function],
  COLUMN_NAME AS [Column],
  DATA_TYPE AS [Data Type],
  CHARACTER_MAXIMUM_LENGTH AS [Char Max Length] 
FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS;

Result:

+------------+----------+-------------------+------------+-------------+-------------------+
| Database   | Schema   | Function          | Column     | Data Type   | Char Max Length   |
|------------+----------+-------------------+------------+-------------+-------------------|
| Music      | dbo      | ufn_AlbumsByGenre | ArtistName | nvarchar    | 255               |
| Music      | dbo      | ufn_AlbumsByGenre | AlbumName  | nvarchar    | 255               |
| Music      | dbo      | ufn_AlbumsByGenre | Genre      | nvarchar    | 50                |
+------------+----------+-------------------+------------+-------------+-------------------+

These results show me that there’s only one table-valued function that I can access in the database, and it returns three columns.

In this example I chose to return only six columns from the view. The following example displays all columns returned by the view.

Example 2 – Return All Columns from the View

In this example I include all columns that the view returns. I also use vertical output so that you aren’t forced to scroll horizontally. Also, for the sake of brevity, I specify for the view to return information about just one column (therefore just one row is returned).

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
WHERE TABLE_NAME = 'ufn_AlbumsByGenre'
AND COLUMN_NAME = 'ArtistName';

Result (using vertical output):

TABLE_CATALOG            | Music
TABLE_SCHEMA             | dbo
TABLE_NAME               | ufn_AlbumsByGenre
COLUMN_NAME              | ArtistName
ORDINAL_POSITION         | 1
COLUMN_DEFAULT           | NULL
IS_NULLABLE              | NO
DATA_TYPE                | nvarchar
CHARACTER_MAXIMUM_LENGTH | 255
CHARACTER_OCTET_LENGTH   | 510
NUMERIC_PRECISION        | NULL
NUMERIC_PRECISION_RADIX  | NULL
NUMERIC_SCALE            | NULL
DATETIME_PRECISION       | NULL
CHARACTER_SET_CATALOG    | NULL
CHARACTER_SET_SCHEMA     | NULL
CHARACTER_SET_NAME       | UNICODE
COLLATION_CATALOG        | NULL
COLLATION_SCHEMA         | NULL
COLLATION_NAME           | SQL_Latin1_General_CP1_CI_AS
DOMAIN_CATALOG           | NULL
DOMAIN_SCHEMA            | NULL
DOMAIN_NAME              | NULL

See the Microsoft documentation for information about each column returned by this view.