CODEWITHSUNDEEP
sql-serversql-server-2019
Attila Szeremi
Attila Szeremi

Reputation: 5478

How to grab information of a SQL Server user-defined function's parameters and return type?

I have a user-defined function:

CREATE FUNCTION [foo].[bar]
(   
  -- ...parameters
)
RETURNS TABLE

AS 

RETURN
SELECT ...

How can I get information about (the types of) the fields returned by this function, i.e. the return schema? Also, how do I get information about the parameters?

Upvotes: 1

Views: 222

Answers (1)

Stuck at 1337
Stuck at 1337

Reputation: 2084

You can use sys.dm_exec_describe_first_result_set:

SELECT column_ordinal, system_type_name, max_length, precision, scale
  FROM sys.dm_exec_describe_first_result_set
    ('SELECT * FROM [foo].[bar](1)', null, null);

You'll need to include the right types of parameters to the function to make the call valid.

And for the parameters, use sys.parameters and sys.types:

SELECT p.name, t.name, p.max_length, p.precision, p.scale
  FROM sys.parameters AS p
  INNER JOIN sys.types AS t
  ON p.system_type_id = t.system_type_id;

To do the former without having to first do the latter manually:

DECLARE @func nvarchar(1000) = N'foo.bar', 

DECLARE @obj int = object_id(@func),
        @command nvarchar(max) = N'';

SELECT @command = N'SELECT column_ordinal, system_type_name, 
    max_length, precision, scale
  FROM sys.dm_exec_describe_first_result_set(
    ''SELECT * FROM ' + @func + N'('
      + STRING_AGG('default', char(44)) + N')'', null, null);'
  FROM sys.parameters
  WHERE object_id = @obj
  AND parameter_id >= 1;

EXECUTE sp_executesql @command;

Upvotes: 3

Related Questions