SQL Server 2012 T-SQL at a Glance - Metadata Discovery

SQL Server 2012 (formerly code-named Denali) introduces new metadata discovery capabilities, removing the need to use the older SET FMTONLY option. In fact, the latter is deprecated and support for it will be removed from a future version of SQL Server. The new metadata discovery options include the stored procedure sys.sp_describe_first_result_set, the functions sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object, and the stored procedure sys.sp_describe_undeclared_parameters.

I’ll start with the stored procedure sp_describe_first_result_set. This procedure describes the first possible result set in the input batch. Describing a result set means returning metadata information about the columns in the result set. The procedure accepts three inputs: @tsql is the input T-SQL batch, @params is a declaration of the parameters used in the batch, and @include_browse_information is a bit indicating whether to include browse information. Here’s an example for using the procedure with a query that refers to a parameter, a declaration of that parameter, and a request to include browse information:

SET NOCOUNT ON;
USE AdventureWorks2008R2;
GO

EXEC sys.sp_describe_first_result_set
  @tsql = N'SELECT OrderDate, TotalDue, CurrencyRateID
            FROM Sales.SalesOrderHeader
            WHERE SalesOrderID = @orderid;',
  @params = N'@orderid AS INT',
  @include_browse_information = 1;

is_hidden column_ordinal name           is_nullable system_type_id
--------- -------------- -------------- ----------- --------------
0         1              OrderDate      0           61            
0         2              TotalDue       0           60            
0         3              CurrencyRateID 1           56            
1         4              SalesOrderID   0           56            

system_type_name max_length precision scale collation_name
---------------- ---------- --------- ----- --------------
datetime         8          23        3     NULL          
money            8          19        4     NULL          
int              4          10        0     NULL          
int              4          10        0     NULL          

user_type_id user_type_database user_type_schema
------------ ------------------ ----------------
NULL         NULL               NULL            
NULL         NULL               NULL            
NULL         NULL               NULL            
NULL         NULL               NULL            

user_type_name assembly_qualified_type_name xml_collection_id
-------------- ---------------------------- -----------------
NULL           NULL                         NULL              
NULL           NULL                         NULL              
NULL           NULL                         NULL              
NULL           NULL                         NULL              

xml_collection_database xml_collection_schema xml_collection_name
----------------------- --------------------- -------------------
NULL                    NULL                  NULL                
NULL                    NULL                  NULL                
NULL                    NULL                  NULL                
NULL                    NULL                  NULL                

is_xml_document is_case_sensitive is_fixed_length_clr_type
--------------- ----------------- ------------------------
0               0                 0                        
0               0                 0                        
0               0                 0                        
0               0                 0                        

source_server source_database      source_schema source_table    
------------- -------------------- ------------- ----------------
NULL          AdventureWorks2008R2 Sales         SalesOrderHeader
NULL          AdventureWorks2008R2 Sales         SalesOrderHeader
NULL          AdventureWorks2008R2 Sales         SalesOrderHeader
NULL          AdventureWorks2008R2 Sales         SalesOrderHeader

source_column  is_identity_column is_part_of_unique_key
-------------- ------------------ ---------------------
OrderDate      0                  0                    
TotalDue       0                  0                    
CurrencyRateID 0                  0                    
SalesOrderID   1                  1                    

is_updateable is_computed_column is_sparse_column_set
------------- ------------------ --------------------
1             0                  0                    
0             1                  0                    
1             0                  0                    
0             0                  0                    

ordinal_in_order_by_list order_by_is_descending order_by_list_length
------------------------ ---------------------- --------------------
NULL                     NULL                   NULL                
NULL                     NULL                   NULL                
NULL                     NULL                   NULL                
NULL                     NULL                   NULL                

tds_type_id tds_length  tds_collation_id tds_collation_sort_id
----------- ----------- ---------------- ---------------------
61          8           NULL             NULL
60          8           NULL             NULL
38          4           NULL             NULL
56          4           NULL             NULL

As you can see, quite a lot of metadata information is provided. The request to include browse information means that the query is treated as if it included the FOR BROWSE option. The output includes information about key columns, even if those columns are not included in the query output, as well as information about the source tables, which is not included by default.

If a key column is not included in the query output but you requested to include browse information, the procedure’s output about the key column will indicate 1 in the is_hidden attribute, as is the case in the output of our example for the column SalesOrderID. The additional information about the source tables includes source_database, source_schema, source_table and source_column. Rerun the previous example, only ask not to include browse information, like so:

EXEC sys.sp_describe_first_result_set
  @tsql = N'SELECT OrderDate, TotalDue, CurrencyRateID
            FROM Sales.SalesOrderHeader
            WHERE SalesOrderID = @orderid;',
  @params = N'@orderid AS INT',
  @include_browse_information = 0;

This time the output does not include information about SalesOrderID, and the output attributes source_database, source_schema and source_table and source_column are all NULLs.

SQL Server Denali also introduces a function called sys.dm_exec_describe_first_result_set that gives you the same information as the procedure sp_describe_first_result_set, only in a form that you query. Also, the function has the advantage that you can request only the output attributes of interest, e.g.:

SELECT
  is_hidden, column_ordinal, name, is_nullable, system_type_id,
  system_type_name, max_length, precision, scale, collation_name,
  source_database, source_schema, source_table, source_column
FROM sys.dm_exec_describe_first_result_set(
  N'SELECT OrderDate, TotalDue, CurrencyRateID
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID = @orderid;',
  N'@orderid AS INT',
  0);

Back to the sp_describe_first_result_set procedure, there are all kinds of subtleties involving cases where in different conditions a different first result set can be returned

Books Online goes into the details of the different cases, but just to give you a sense, I’ll show a couple of examples.

If there are different possibilities for the first result set due to some conditional logic, but all have the same metadata, the procedure will report the metadata that they all share successfully. Here’s an example where the conditional logic is only concerned with different sorting options:

EXEC sys.sp_describe_first_result_set
  @tsql = N'IF @direction = ''ASC''
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
              ORDER BY SalesOrderID
            ELSE IF @direction = ''DESC''
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
              ORDER BY SalesOrderID DESC
            ELSE
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
              ORDER BY SalesOrderID;',
  @params = N'@direction AS VARCHAR(4)';

is_hidden column_ordinal name           is_nullable system_type_id
--------- -------------- -------------- ----------- --------------
0         1              SalesOrderID   0           56            
0         2              OrderDate      0           61            
0         3              TotalDue       0           60            
0         4              CurrencyRateID 1           56            

system_type_name max_length precision scale collation_name
---------------- ---------- --------- ----- --------------
int              4          10        0     NULL          
datetime         8          23        3     NULL          
money            8          19        4     NULL          
int              4          10        0     NULL          

However, if the metadata of the different possibilities for the first result set are different, depending on how different, the procedure will either raise an error or return output after resolving mismatches. For example, in the following batch there are two possibilities for the first result set, and the two are incompatible in the number of result columns as well as in other ways, hence the procedure generates an error:

EXEC sys.sp_describe_first_result_set
  @tsql = N'IF @request = 1
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
            ELSE IF @request = 2
              SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice
              FROM Sales.SalesOrderDetail;',
  @params = N'@request AS INT';

Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader' is not compatible with the statement 'SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice
              FROM Sales.Sa'.

But as mentioned, in some cases the procedure will not generate an error but rather just apply logic to figure out how to resolve mismatches. For example, if between two corresponding columns one is NULLable and one isn’t, the output will show the column as allowing NULLs. If the column names of corresponding columns are different, the result will have a NULL in the column name.

When existing metadata for a result column is not available from the system catalog, e.g., when the column is based on an expression made of literals, sp_describe_first_result_set will still generate metadata. As an example, here’s a query that returns four columns all based on literals:

EXEC sys.sp_describe_first_result_set
  @tsql = N'SELECT ''abc'' AS col1, 1 AS col2, 12.3 AS col3, 3000000000;';

-- abbreviated output
is_hidden column_ordinal name  is_nullable system_type_id
--------- -------------- ----- ----------- --------------
0         1              col1  0           167            
0         2              col2  0           56            
0         3              col3  0           108            
0         4              NULL  0           108            

system_type_name  max_length precision scale collation_name
----------------- ---------- --------- ----- -------------------------
varchar(3)        3          0         0     Latin1_General_100_CS_AS
int               4          10        0     NULL
numeric(3,1)      5          3         1     NULL
numeric(10,0)     9          10        0     NULL

Interesting to see that the procedure uses a minimalistic approach in terms of maximum length, precision, scale, etc.

SQL Server 2012 also provides a procedure called dm_exec_describe_first_result_set_for_object that provides metadata description for a stored procedure or trigger whose id is given as input. As an example, run the following code to create a stored procedure called GetOrderInfo:

IF OBJECT_ID('dbo.GetOrderInfo', 'P') IS NOT NULL DROP PROC dbo.GetOrderInfo;
GO

CREATE PROC dbo.GetOrderInfo
  @orderid AS INT
AS

SELECT OrderDate, TotalDue, CurrencyRateID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @orderid;
GO

Then use the following code to describe the GetOrderInfo procedure’s metadata:

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.GetOrderInfo'), 1) ;

Finally, SQL Server Denali also introduces a stored procedure called sp_describe_undeclared_parameters that tries to guess metadata information of undeclared parameters in the code based on context. Here’s an example for using this procedure:

EXEC sys.sp_describe_undeclared_parameters
  @tsql = N'SELECT OrderDate, TotalDue
            FROM Sales.SalesOrderHeader
            WHERE SalesOrderNumber = @SalesOrderNumber;';

parameter_ordinal name               suggested_system_type_name  
----------------- ------------------ ---------------------------
1                 @SalesOrderNumber  nvarchar(25)                

suggested_max_length suggested_precision suggested_scale
-------------------- ------------------- ---------------
50                   0                   0              

suggested_user_type_id suggested_user_type_database
---------------------- ----------------------------
NULL                   NULL                        

suggested_user_type_schema suggested_user_type_name
-------------------------- ------------------------
NULL                       NULL                    

suggested_assembly_qualified_type_name suggested_xml_collection_id
-------------------------------------- ---------------------------
NULL                                   NULL                        

suggested_xml_collection_database suggested_xml_collection_schema
--------------------------------- -------------------------------
NULL                              NULL                            

suggested_xml_collection_name suggested_is_xml_document
----------------------------- -------------------------
NULL                          0                        

suggested_is_case_sensitive suggested_is_fixed_length_clr_type suggested_is_input
--------------------------- ---------------------------------- ------------------
1                           0                                  1                  

suggested_is_output formal_parameter_name suggested_tds_type_id suggested_tds_length
------------------- --------------------- --------------------- --------------------
0                   NULL                  231                   50

As you can see, the procedure figured out the parameter’s metadata based on context—the predicate in the query’s filter that compares the SalesOrderNumber attribute with the @SalesOrderNumber parameter.

For more information about metadata browsing, check out SQL Server 2012's Books Online.

Cheers,

BG

Discuss this Blog Entry 1

on Apr 4, 2011
The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects
SQL Reports
URL 1: http://www.sql-reports.net/
Sql tutorial
http://www.sql-reports.net/2011/03/sql-select-tutorials.html







Please or Register to post comments.

What's Puzzled By T-SQL Blog?

T-SQL tips and logical puzzles from Itzik Ben-Gan.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×