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?

• Dan Guzman: FWIW, I can repro with the table variable like your example but it works as expected with a permanent table. Maybe someone can explain why.
• Peter Vandivier: Interesting question. Consider the differences between table variables, temp tables, and permanent tables (see dbfiddle.uk/…). Would you care to edit some of that detail into your question (or would you mind if I take the liberty of doing so)? Note also the availability of sys.dm_exec_describe_first_result_set for a narrower result set while rubber-ducking
• Mark: @PeterVandivier feel free to improve up my question

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.

• Mark: Thanks for your help. It is indeed not a practical solution in my case. I'll keep on digging and consider your solution as last chance.