Get return type of SQL Server function

Question

In SQL Server, a function is defined as follows:

-- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
 [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Where return_data_type can be text, a table (with a slightly different syntax), or almost any other data type.

Is it possible to retrieve the return data type without running the query?

I know it's possible to do using sp_describe_first_result_set, but this executes the query and looks at the response. Edit: I was wrong. It is done through static analysis, but has a number of limitations associated with it.

Answer 1

As mentioned in comments, you can use sp_describe_first_result_set.

Or you can use the query from the linked duplicate and extend it with INFORMATION_SCHEMA.ROUTINE_COLUMNS:

SELECT r.ROUTINE_NAME AS FunctionName,
       r.DATA_TYPE AS FunctionReturnType,
       rc.COLUMN_NAME AS ColumnName,
       rc.DATA_TYPE AS ColumnType
FROM   INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN INFORMATION_SCHEMA.ROUTINE_COLUMNS rc ON rc.TABLE_NAME = r.ROUTINE_NAME
WHERE  ROUTINE_TYPE = 'FUNCTION'
ORDER BY r.ROUTINE_NAME, rc.ORDINAL_POSITION;

That will give you the return information for both scalar-value functions and table-value functions, including schema information for the TVF result set.