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:

  1. SET NOCOUNT ON;
  2. USE AdventureWorks2008R2;
  3. GO
  4.  
  5. EXEC sys.sp_describe_first_result_set
  6.   @tsql = N'SELECT OrderDate, TotalDue, CurrencyRateID
  7.             FROM Sales.SalesOrderHeader
  8.             WHERE SalesOrderID = @orderid;',
  9.   @params = N'@orderid AS INT',
  10.   @include_browse_information = 1;
  11.  
  12. is_hidden column_ordinal name           is_nullable system_type_id
  13. --------- -------------- -------------- ----------- --------------
  14. 0         1              OrderDate      0           61            
  15. 0         2              TotalDue       0           60            
  16. 0         3              CurrencyRateID 1           56            
  17. 1         4              SalesOrderID   0           56            
  18.  
  19. system_type_name max_length precision scale collation_name
  20. ---------------- ---------- --------- ----- --------------
  21. datetime         8          23        3     NULL          
  22. money            8          19        4     NULL          
  23. int              4          10        0     NULL          
  24. int              4          10        0     NULL          
  25.  
  26. user_type_id user_type_database user_type_schema
  27. ------------ ------------------ ----------------
  28. NULL         NULL               NULL            
  29. NULL         NULL               NULL            
  30. NULL         NULL               NULL            
  31. NULL         NULL               NULL            
  32.  
  33. user_type_name assembly_qualified_type_name xml_collection_id
  34. -------------- ---------------------------- -----------------
  35. NULL           NULL                         NULL              
  36. NULL           NULL                         NULL              
  37. NULL           NULL                         NULL              
  38. NULL           NULL                         NULL              
  39.  
  40. xml_collection_database xml_collection_schema xml_collection_name
  41. ----------------------- --------------------- -------------------
  42. NULL                    NULL                  NULL                
  43. NULL                    NULL                  NULL                
  44. NULL                    NULL                  NULL                
  45. NULL                    NULL                  NULL                
  46.  
  47. is_xml_document is_case_sensitive is_fixed_length_clr_type
  48. --------------- ----------------- ------------------------
  49. 0               0                 0                        
  50. 0               0                 0                        
  51. 0               0                 0                        
  52. 0               0                 0                        
  53.  
  54. source_server source_database      source_schema source_table    
  55. ------------- -------------------- ------------- ----------------
  56. NULL          AdventureWorks2008R2 Sales         SalesOrderHeader
  57. NULL          AdventureWorks2008R2 Sales         SalesOrderHeader
  58. NULL          AdventureWorks2008R2 Sales         SalesOrderHeader
  59. NULL          AdventureWorks2008R2 Sales         SalesOrderHeader
  60.  
  61. source_column  is_identity_column is_part_of_unique_key
  62. -------------- ------------------ ---------------------
  63. OrderDate      0                  0                    
  64. TotalDue       0                  0                    
  65. CurrencyRateID 0                  0                    
  66. SalesOrderID   1                  1                    
  67.  
  68. is_updateable is_computed_column is_sparse_column_set
  69. ------------- ------------------ --------------------
  70. 1             0                  0                    
  71. 0             1                  0                    
  72. 1             0                  0                    
  73. 0             0                  0                    
  74.  
  75. ordinal_in_order_by_list order_by_is_descending order_by_list_length
  76. ------------------------ ---------------------- --------------------
  77. NULL                     NULL                   NULL                
  78. NULL                     NULL                   NULL                
  79. NULL                     NULL                   NULL                
  80. NULL                     NULL                   NULL                
  81.  
  82. tds_type_id tds_length  tds_collation_id tds_collation_sort_id
  83. ----------- ----------- ---------------- ---------------------
  84. 61          8           NULL             NULL
  85. 60          8           NULL             NULL
  86. 38          4           NULL             NULL
  87. 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:

  1. EXEC sys.sp_describe_first_result_set
  2.   @tsql = N'SELECT OrderDate, TotalDue, CurrencyRateID
  3.             FROM Sales.SalesOrderHeader
  4.             WHERE SalesOrderID = @orderid;',
  5.   @params = N'@orderid AS INT',
  6.   @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.:

  1. SELECT
  2.   is_hidden, column_ordinal, name, is_nullable, system_type_id,
  3.   system_type_name, max_length, precision, scale, collation_name,
  4.   source_database, source_schema, source_table, source_column
  5. FROM sys.dm_exec_describe_first_result_set(
  6.   N'SELECT OrderDate, TotalDue, CurrencyRateID
  7.     FROM Sales.SalesOrderHeader
  8.     WHERE SalesOrderID = @orderid;',
  9.   N'@orderid AS INT',
  10.   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:

  1. EXEC sys.sp_describe_first_result_set
  2.   @tsql = N'IF @direction = ''ASC''
  3.               SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
  4.               FROM Sales.SalesOrderHeader
  5.               ORDER BY SalesOrderID
  6.             ELSE IF @direction = ''DESC''
  7.               SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
  8.               FROM Sales.SalesOrderHeader
  9.               ORDER BY SalesOrderID DESC
  10.             ELSE
  11.               SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
  12.               FROM Sales.SalesOrderHeader
  13.               ORDER BY SalesOrderID;',
  14.   @params = N'@direction AS VARCHAR(4)';
  15.  
  16. is_hidden column_ordinal name           is_nullable system_type_id
  17. --------- -------------- -------------- ----------- --------------
  18. 0         1              SalesOrderID   0           56            
  19. 0         2              OrderDate      0           61            
  20. 0         3              TotalDue       0           60            
  21. 0         4              CurrencyRateID 1           56            
  22.  
  23. system_type_name max_length precision scale collation_name
  24. ---------------- ---------- --------- ----- --------------
  25. int              4          10        0     NULL          
  26. datetime         8          23        3     NULL          
  27. money            8          19        4     NULL          
  28. 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:

  1. EXEC sys.sp_describe_first_result_set
  2.   @tsql = N'IF @request = 1
  3.               SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
  4.               FROM Sales.SalesOrderHeader
  5.             ELSE IF @request = 2
  6.               SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice
  7.               FROM Sales.SalesOrderDetail;',
  8.   @params = N'@request AS INT';
  9.  
  10. Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
  11. The metadata could not be determined because the statement 'SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
  12.               FROM Sales.SalesOrderHeader' is not compatible with the statement 'SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice
  13.               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:

  1. EXEC sys.sp_describe_first_result_set
  2.   @tsql = N'SELECT ''abc'' AS col1, 1 AS col2, 12.3 AS col3, 3000000000;';
  3.  
  4. -- abbreviated output
  5. is_hidden column_ordinal name  is_nullable system_type_id
  6. --------- -------------- ----- ----------- --------------
  7. 0         1              col1  0           167            
  8. 0         2              col2  0           56            
  9. 0         3              col3  0           108            
  10. 0         4              NULL  0           108            
  11.  
  12. system_type_name  max_length precision scale collation_name
  13. ----------------- ---------- --------- ----- -------------------------
  14. varchar(3)        3          0         0     Latin1_General_100_CS_AS
  15. int               4          10        0     NULL
  16. numeric(3,1)      5          3         1     NULL
  17. 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:

  1. IF OBJECT_ID('dbo.GetOrderInfo', 'P') IS NOT NULL DROP PROC dbo.GetOrderInfo;
  2. GO
  3.  
  4. CREATE PROC dbo.GetOrderInfo
  5.   @orderid AS INT
  6. AS
  7.  
  8. SELECT OrderDate, TotalDue, CurrencyRateID
  9. FROM Sales.SalesOrderHeader
  10. WHERE SalesOrderID = @orderid;
  11. GO

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

  1. SELECT *
  2. FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.GetOrderInfo'), 1) ;
  3.  
  4. 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:
  5.  
  6. EXEC sys.sp_describe_undeclared_parameters
  7.   @tsql = N'SELECT OrderDate, TotalDue
  8.             FROM Sales.SalesOrderHeader
  9.             WHERE SalesOrderNumber = @SalesOrderNumber;';
  10.  
  11. parameter_ordinal name               suggested_system_type_name  
  12. ----------------- ------------------ ---------------------------
  13. 1                 @SalesOrderNumber  nvarchar(25)                
  14.  
  15. suggested_max_length suggested_precision suggested_scale
  16. -------------------- ------------------- ---------------
  17. 50                   0                   0              
  18.  
  19. suggested_user_type_id suggested_user_type_database
  20. ---------------------- ----------------------------
  21. NULL                   NULL                        
  22.  
  23. suggested_user_type_schema suggested_user_type_name
  24. -------------------------- ------------------------
  25. NULL                       NULL                    
  26.  
  27. suggested_assembly_qualified_type_name suggested_xml_collection_id
  28. -------------------------------------- ---------------------------
  29. NULL                                   NULL                        
  30.  
  31. suggested_xml_collection_database suggested_xml_collection_schema
  32. --------------------------------- -------------------------------
  33. NULL                              NULL                            
  34.  
  35. suggested_xml_collection_name suggested_is_xml_document
  36. ----------------------------- -------------------------
  37. NULL                          0                        
  38.  
  39. suggested_is_case_sensitive suggested_is_fixed_length_clr_type suggested_is_input
  40. --------------------------- ---------------------------------- ------------------
  41. 1                           0                                  1                  
  42.  
  43. suggested_is_output formal_parameter_name suggested_tds_type_id suggested_tds_length
  44. ------------------- --------------------- --------------------- --------------------
  45. 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) ×