Earlier, we discussed a situation wherein the database objects (an ad-hoc query, stored procedures, functions, etc) need to supply meta-data to the calling applications so that the format of the response can be tested without actually running the query. ADO uses this mechanism to make a “dry-run”.

In SQL Server 2008 R2 and below, one SET option came in very handy in realizing this requirement. This option was the FMTONLY option. The FMTONLY option is marked as a deprecated feature in SQL Server 2012, and is replaced by:

• sp_describe_first_result_set
• sys.dm_exec_describe_first_result_set
• sys.dm_exec_describe_first_result_set_for_object

sp_describe_first_result_set

You can revisit the discussion related to FMTONLY here. The core requirement is that at execute time, the SQL Server database engine should parse (PARSEONLY), compile (NOEXEC) and executed a query, but return only meta-data back to the client application. No data rows are to be returned. Please note that the name of the procedure suggests that information is returned only for the first result set (this could be applicable in case your procedures return more than one result set/MARS).

Here’s how the sp_describe_first_result_set stored procedure can be used to get this information:

USE AdventureWorks2012
GO
--SQL 2012 and above only!
EXEC sp_describe_first_result_set @tsql = N'SELECT Employee.BusinessEntityID,
                                                   Employee.BirthDate,
                                                   Employee.Gender,
                                                   Employee.JobTitle
                                            FROM HumanResources.Employee AS Employee'
GO

One of the first things we see is that the result set is much more comprehensive and user-friendly when compared with the older FMTONLY.

The extended stored procedure also accepts:

• A set of parameters (@params), similar to sp_executesql
• A @browse_information_mode parameter which has the following possible values:
  • 0 = No information is returned
  • 1 = Query is analyzed as if it contains the FOR BROWSE option specified
  • 2 = Query is analyzed as if it would be used in preparing or executing a cursor

Return Value information:

1. When called from T-SQL, sp_describe_first_result_set will always have a return value of 0
2. If the procedure throws an error and the procedure is called as an RPC, return status is populated by the type of error described in the error_type column of sys.dm_exec_describe_first_result_set

sys.dm_exec_describe_first_result_set

This is the DMV equivalent of sp_describe_first_result_set. Sample execution is as shown below:

USE AdventureWorks2012
GO
SELECT * 
FROM sys.dm_exec_describe_first_result_set(N'SELECT Employee.BusinessEntityID,
                                                    Employee.BirthDate,
                                                    Employee.Gender,
                                                    Employee.JobTitle
                                            FROM HumanResources.Employee AS Employee',
                                            NULL,
                                            0)
GO

As you may have noticed, the function takes 3 parameters which are same as that shown above for sp_describe_first_result_set. The result set is also the same.

sys.dm_exec_describe_first_result_set_for_object

Functionally equivalent to sys.dm_exec_describe_first_result_set dynamic management function, sys.dm_exec_describe_first_result_set_for_object accepts an Object Id as input. This Object Id can be that of a T-SQL stored procedure or a T-SQL trigger. If it is the ID of any other object (such as a view, table, function, or CLR procedure), an error will be specified in the error columns of the result.

The result set is same as that of sys.dm_exec_describe_first_result_set.

Please NOTE:

Since these procedures/functions return meta-data about the return result-set, one of the following will be true if the batch is executed after execution of sp_describe_first_result_set:

1. Result in an optimization-time error
2. Result in a run-time error (depending upon the parameters passed or the state of the server/database, or another general run-time error)
3. Return no result set (depending upon the parameters passed)
4. Returns a result-set conforming to the same meta-data

References:

1. sp_describe_first_result_set – http://msdn.microsoft.com/en-us/library/ff878602.aspx
2. sys.dm_exec_describe_first_result_set – http://msdn.microsoft.com/en-us/library/ff878258.aspx
3. sys.dm_exec_describe_first_result_set_for_object – http://msdn.microsoft.com/en-us/library/ff878236.aspx

Until we meet next time,

Be courteous. Drive responsibly.