SQL Server sp_describe_first_result_set: find user defined types

Question

I need to find the column definitions from a stored procedure.The docs for sp_describe_first_result_set show an entry for user_type_name where I'd expect to see my User Defined Types. Sadly, data for UDTs seems to be missing from the procedure result. Given the sample code:

CREATE PROCEDURE [dbo].[test]
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @test TABLE (
        a BIGINT
        ,b udtTest
        ,c NVARCHAR(100)
        ,d BIT
    )

    INSERT INTO @test (
        a, b, c, d
    ) VALUES (
        1, 'udtTest', 'nvarchar', 0
    )

    select *
    from @test
END
GO
EXEC [dbo].[sp_describe_first_result_set] @tsql = N'test';
GO

I'm seeing the following (db<>fiddle):

name	system_type_name	user_type_name
a	bigint	NULL
b	nvarchar(123)	NULL
c	nvarchar(100)	NULL
d	bit	NULL

Why is udtTest missing from my data for column name b?

Answer 1

This appears not to work as advertised. Temporary tables are disallowed, but table variables should work just fine. Alias types are a bit of an edge case, so perhaps a bug crept in here unnoticed. If it is a blocker for you, open a support case with Microsoft.

There aren't any perfect workarounds (that I am aware of). One of the best ones is to create a wrapper procedure that uses WITH RESULT SETS to define the output shape:

CREATE TYPE dbo.udtTest FROM nvarchar(123) NOT NULL;
GO
CREATE OR ALTER PROCEDURE dbo.P AS
SET NOCOUNT ON;
DECLARE @T TABLE (udt dbo.udtTest);
INSERT @T VALUES (N'Banana');
SELECT T.udt FROM @T AS T;
GO
-- No user type info
EXECUTE sys.sp_describe_first_result_set 
    @tsql = N'EXECUTE Sandpit.dbo.P;', 
    @parameters = NULL, 
    @browse_information_mode = 0;

-- Wrapper for dbo.P
CREATE OR ALTER PROCEDURE dbo.P_Wrapper AS
SET NOCOUNT ON;
EXECUTE dbo.P 
    WITH RESULT SETS ((udt dbo.udtTest NOT NULL));
GO
-- Works!
EXECUTE sys.sp_describe_first_result_set 
    @tsql = N'EXECUTE dbo.P_Wrapper;', 
    @parameters = NULL, 
    @browse_information_mode = 0;

This may or may not be convenient or practical for you. It's probably the best we can do until the bug (?) is fixed, or Microsoft extend CREATE PROCEDURE syntax to include WITH RESULT SETS, as we have been asking for a long time now.