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