Wednesday, October 30, 2013

Exploring SQL Server 2012's sys.dm_exec_describe_first_result_set_for_object()

SQL Server 2012 introduces sys.dm_exec_describe_first_result_set_for_object(). The official documentation is here.

Let's work through some examples to discover what this new function delivers.

First, build a table that will be used to illustrate the function:
CREATE TABLE dbo.TestTable (
    
ColID       INT                     IDENTITY,
    
ColStr      CHAR(10)                NOT NULL,
    
ColMod              AS ColID % 10,
    
ColDef      bit                     NOT NULL    DEFAULT 0,
    
ColCalc0    INT                     NULL,
    
ColDec      DECIMAL(3,2)            NULL,
    
CONSTRAINT UIX_Test1_ColStr UNIQUE (ColStr)
);

Then build the simplest possible stored procedure that references that table:
CREATE PROCEDURE dbo.SimpleProc AS
SELECT
ColID FROM dbo.TestTable;

Now call the function:
SELECT * 
FROM sys.dm_exec_describe_first_result_set_for_object (
    
OBJECT_ID('dbo.SimpleProc'),
    
0

);

Remember that this stored procedures selects only one column. There are many columns in the result set. Here are some of the more interesting ones:
is_hidden:                0
column_ordinal:           1
is_nullable:              0
system_type_name:         INT
is_identity_column:       1
is_updateable:            0
is_computed_column:       0
ordinal_in_order_by_list: NULL
order_by_is_descending:   NULL
asdf






No comments:

Post a Comment