SP_DESCRIBE_FIRST_RESULT_SET to view result set metadata

      When I was looking for what’s new in the SQL server 2012, one small and interesting item I found that SET FMTONLY and sp_describe_first_result_set, let’s have a look:

SET FMTONLY: This setting has two values ON and OFF, when it is ON client only can see the format of the result set, not data. This is for run time setting, not compile time. Below is the example: 

SET FMTONLY OFF

GO 

SELECT * 

  FROM [AdventureWorks2008R2].[HumanResources].[Employee]

SET FMTONLY ON

GO 

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT * 

  FROM [AdventureWorks2008R2].[HumanResources].[Employee]

Note: As per MSDN update do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

SP_DESCRIBE_FIRST_RESULT_SET: This procedure is more advance and useful than FMTONLY. This procedure returns the metadata information of the first possible result set, with complete detail. This procedure takes statement as dynamic query. You can pass multiple query at once. This will return the meta-Data information of the first possible result set. Below is the example:

 EXEC sp_describe_first_result_set

 @tsql = N'select * from [AdventureWorks2008R2].[Sales].[vSalesPersonSalesByFiscalYears];'