SQL Server 2012: TSQL Metadata Discovery May 1st, 2013

Vinod Kumar

Prior to SQL Server 2012, when applications needed to retrieve column metadata for the first result set in a batch they used the FMTONLY set option (details BOL MSDN). This mode allows a user to retrieve metadata for result sets without actually executing the batch. This approach can be inconsistent or error prone in its execution and let me give you a simple example below:



IF (1 = 2)

     SELECT * FROM Person.Person;


     SELECT * FROM Person.Address;




Can you see the problem here? We get two result set and if the application is using the first result set metadata information then it will yield a wrong result and ultimately there will be errors. Now that we have understood this behavior, let us move to what has been introduced with SQL Server 2012. 


SQL Server 2012 introduces this new option and extends the EXECUTE command with a new execution option called as “WITH RESULT SETS <options>”. Using this new option, you can now define the metadata for result set that will be returned by a batch, stored procedure or dynamic SQL (if any). The default option is "RESULT SETS UNDEFINED", which indicates that any type of result set may be returned by dynamic SQL or none at all.

A typical usage is to run the below query on AdventureWorks database – uspGetManagerEmployees has one parameter and it helps find all the people that roll-up to the employee.

EXEC [dbo].[uspGetManagerEmployees] 3

Now this gives a resultset with column names like RecursionLevel, OrganizationNode … etc. Now that is of no use if we look at it from the application point of view. We want something more understandable format. Here is how we will rewrite the same query with meaningful column names.

EXEC [dbo].[uspGetManagerEmployees] 3 


— This helps redefine column metadata to better

— indicate the result set’s business purpose


   ([Reporting Level] int NOT NULL,

    [Employee ID of Manager] nvarchar(max) NOT NULL,

    [Manager First Name] nvarchar(50) NOT NULL,

    [Manager Last Name] nvarchar(50) NOT NULL,

    [ID of Employee] int NOT NULL,

    [Employee First Name] nvarchar(50) NOT NULL,

    [Employee Last Name] nvarchar(50) NOT NULL)  


The neat trick I saw is, we can also pre-define our result set as table data type and use that as possible result set column metadata using AS TYPE <table type> option. That was really cool and something interesting to know.

Datatype Conversions

If data type (metadata) for a pertinent column does not match between what is returned by query and what is defined by "WITH RESULT SETS <>" option, an attempt is made to convert the data returned by query to what is defined by the execute option implicitly. If the implicit conversion is not possible, an error is raised.

Below are a typical example of implicit conversion(Query 1) and an Error (Query 2).

— Query 1

EXECUTE (N’SELECT 007 as output’)



     ([VALUE] money)




— Query 2 – Conversion ERROR

EXECUTE (N’SELECT N”BigData” as output’)



     ([VALUE] float)


The output is:



When we are on this topic, thought it would be worth to mention about a system stored procedure “sp_describe_first_result_set” and dynamic management view “sys.dm_exec_describe_first_result_set”. The system stored procedure and dynamic management view return the metadata for the first possible result set of the Transact-SQL batch. They return an empty result set if the batch returns no results. They will raise an error if the Database Engine cannot determine the metadata for the first query that will be executed by performing a static analysis.

Below is a typical use of identifying the metadata for 4 columns (object_id, name, type, type_desc) from the sys.objects system table.

EXECUTE sp_describe_first_result_set

N’SELECT object_id, name, type, type_desc

FROM sys.objects’, null, 0

We can perform the same operation on user defined tables too on the database. This is similar to the first example we saw with FMTONLY.

Though these are the two most used functions, there is one more function to know – sp_describe_undeclared_parameters. This stored procedure returns a result set that contains metadata about undeclared parameters in a TSQL batch. A typical example would look like:

EXECUTE sp_describe_undeclared_parameters

@tsql = N’SELECT object_id, name, type, type_desc

FROM sys.objects

WHERE object_id = @id OR NAME = @name’,

@params = N’@id int’

This function considers each parameter that is used in the @tsql batch, but was not declared in @params. It returns one row for each such parameter, with the deduced type information for that parameter. So in our example above, @id is already defined, so result set has one row describing suggested data type of @name parameter.

Final Words

Some final thoughts:

  1. The new Metadata discovery APIs only return metadata for the "first" result set of the batch, unlike the FMTONLY option which returns result set metadata for all possible queries within the batch.
  2. When you execute a batch/dynamic SQL with "WITH RESULT SETS <>" execute option and if the number or type of result sets returned by batch/dynamic SQL do not match the defined option, we will receive an error.

Hope this was an interesting learning and do let me know if you are using the same already in your environment. Would love to know the scenario’s you are using the same.

Tags: , , , , , , , ,

This entry was posted on Wednesday, May 1st, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “SQL Server 2012: TSQL Metadata Discovery”

  1. Srinivas says:

    Hi Vinod,

    That was an interesting read. Would like to know if this is a deprecated or discontinued feature. We are working on a conversion project where we are handling only discontinued features.

Leave a Reply