1

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?

Attila Szeremi
  • 5,235
  • 5
  • 40
  • 64

1 Answers1

3

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;
Stuck at 1337
  • 1,900
  • 1
  • 3
  • 13
  • Thanks... Is there a way to get the result schema without calling the function i.e. not having to know anything about the parameters? – Attila Szeremi Sep 26 '22 at 14:41
  • 1
    The right types are not strictly needed, their number suffices. You can use `string_agg('default', ',')` on `sys.parameters where parameter_id > 0` to construct a call. This requires some jiggery-pokery with `QUOTENAME` and `sys.schemas` and `sys.objects` to make fully robust, have an upvote in advance. :P – Jeroen Mostert Sep 26 '22 at 14:43
  • @JeroenMostert Thanks for the nudge, I initially resisted that because of all the mess with determining various type properties, I didn't even think of just jamming `default` everywhere. Cheers! – Stuck at 1337 Sep 26 '22 at 18:07
  • Thanks. Any way without actually calling the function too? Because in my real world project the function call is very expensive. – Attila Szeremi Sep 27 '22 at 13:23
  • @AttilaSzeremi In the second example, the function is not called. Did you try it? Also, if the function is slow, have you tried to solve _that_ problem? – Stuck at 1337 Sep 27 '22 at 13:31
  • OK, so I've tried your three code examples. The first didn't give me the right results. The second gave me parameters, though for every function, not just the one; but I'm sure I can filter the results somehow. Your third did actually work! Though it didn't include column names. BTW I found another way of doing it; you could include it in your answer: `SELECT * from INFORMATION_SCHEMA.ROUTINE_COLUMNS where TABLE_SCHEMA = 'foo' and TABLE_NAME = 'bar'`. – Attila Szeremi Sep 28 '22 at 11:25
  • @AttilaSzeremi What does "not the right results" mean? Can you show an example function and what you expect to return? Also from what I can tell `INFORMATION_SCHEMA` should really only be used if you absolutely need code that is minimally functional but portable across platforms ... I don't even know if it returns the all of the (correct) data for each of the multiple types of user-defined functions SQL Server supports. – Stuck at 1337 Sep 29 '22 at 16:11