SET FMTONLY ON doesn't work on SQL Server 2017

[sjh37]

From @CNBoland Using SET FMTONLY ON doesn't work on SQL Server 2017. It appears to have been deprecated.

Here's the schema query executed by the template. I'm executing it in Management Studio:

SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;
SET FMTONLY OFF; SET FMTONLY ON;
SELECT * FROM [dbo].[CsvToInt](default, default);
SET FMTONLY OFF; SET FMTONLY OFF;
SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

On SQL Server 2012 (v11.0.5058.0 x64), an empty result set is returned containing the single column, IntValue, as expected. However, on SQL Server 2017 (v14.0.1000.169), only the words, Commands completed successfully., appear and nothing else.

The same schema query with SET FMTONLY ON removed works as expected:

SET FMTONLY OFF; SET NO_BROWSETABLE ON;
SET FMTONLY OFF;
SELECT * FROM [dbo].[CsvToInt](default, default);
SET FMTONLY OFF; SET FMTONLY OFF;
SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

sys.dm_exec_describe_first_result_set also works, and gives more description of the column data types:

SELECT * FROM sys.dm_exec_describe_first_result_set ('select * from  dbo.CsvToInt(default, default)', NULL, 0);
SELECT * FROM sys.dm_exec_describe_first_result_set ('EXEC FFRS.cv_data default', NULL, 0);
SELECT * FROM sys.dm_exec_describe_first_result_set ('EXEC [dbo].[182_test2] default', NULL, 0);
SELECT * FROM sys.dm_exec_describe_first_result_set ('EXEC dbo.InsertRecord default, default', NULL, 0);
SELECT * FROM sys.dm_exec_describe_first_result_set ('EXEC dbo.SpatialTypesNoParams', NULL, 0);
SELECT * FROM sys.dm_exec_describe_first_result_set ('EXEC dbo.XmlDataV1', NULL, 0);

Use

• sp_describe_first_result_set (Transact-SQL)
• sp_describe_undeclared_parameters (Transact-SQL)
• sys.dm_exec_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

[daiplusplus]

I often have sprocs with multiple return sets, but dm_exec_describe_first_result_set does not return information about any extra result sets after the first. Is there a way around that?