Using sys.dm_exec_describe_first_result_set_for_object. The Hows, Whys and Wherefores.

I’ve been working on a project unkindly nicknamed ‘The Gloop’ because the code is a bit amorphous. Basically, it is an approach to documenting SQL Server databases, using the facilities provided such as the metadata views and DMFs. Although it is relatively simple to record the result returned by a table-valued function I’d rather neglected the stored procedures because there was no metadata that could produce the first result set produced by a procedure or trigger.

I’d been silly because there is, of course, an Execution system DMF that does it: sys.dm_exec_describe_first_result_set_for_object(). it takes as its parameter the object_id of a procedure or trigger and describes the first result metadata for the module with that ID. It has the same result set definition as sys.dm_exec_describe_first_result_set.

Why might a developer find this handy? The problem with stored procedures is that you really need to catch the result set produced into a table, using INSERT..EXECUTE. The INSERT statement can use the EXECUTE clause to call a stored procedure that returns the result. If you want to do something like this …

INSERT INTO #OurBillOfMaterials EXECUTE dbo.uspGetBillOfMaterials;

… you are faced with the task of creating that temporary table. Well no problem, if you have this sys.dm_exec_describe_first_result_set_for_object() DMF.

CREATE TABLE #OurBillOfMaterials
  (
  ProductAssemblyID INT NULL,
  ComponentID INT NULL,
  ComponentDesc NVARCHAR(50) NULL,
  TotalQuantity DECIMAL(38, 2) NULL,
  StandardCost MONEY NULL,
  ListPrice MONEY NULL,
  BOMLevel SMALLINT NULL,
  RecursionLevel INT NULL
  );

INSERT INTO #OurBillOfMaterials EXECUTE dbo.uspGetBillOfMaterials 800,
'9/1/2014';

All I did was to execute the code below, put the result in the body of the create statement and tidy up the results with SQL Prompt

This works on SQL Server 2017 or upwards

SELECT Object_Schema_Name(p.object_id)+'.'+p.name AS name,
  String_Agg(
     r.name + ' ' + system_type_name + ' '
       + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'
       + CASE WHEN collation_name IS NULL  
               OR collation_name = DatabasePropertyEx(Db_Name(), 'Collation')
             THEN     '' ELSE ' COLLATE ' + collation_name END,
     ', '
     ) WITHIN GROUP ( ORDER BY column_ordinal asc ) AS result
  FROM sys.procedures AS p
    OUTER APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
  WHERE r.is_hidden = 0 AND error_state IS NULL 
  GROUP BY Object_Schema_Name(p.object_id)+'.'+p.name
  HAVING Object_Schema_Name(p.object_id)+'.'+p.name='dbo.uspGetBillOfMaterials'

If you want a list of all your procedures done, just scrub that last line

For earlier versions such as 2016, try this instead.

SELECT Name, result from
        (SELECT 
                Object_Schema_Name(p.object_id)+'.'+p.name AS Name,
                stuff((SELECT  ', '+ r.name + ' ' + system_type_name + ' '
       + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'
       + CASE WHEN collation_name IS NULL  
               OR collation_name = DatabasePropertyEx(Db_Name(), 'Collation')
             THEN     '' ELSE ' COLLATE ' + collation_name END
     FROM sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
         WHERE r.is_hidden = 0 AND error_state IS NULL
         ORDER BY column_ordinal
     FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,2,'') result
  FROM sys.procedures AS p)f(name, result)
  WHERE result IS NOT NULL
  AND name='dbo.uspGetBillOfMaterials'

Likewise, you can scrub that last line to get the details of the first result set all the procedures or modify it to produce just a subset.

This is an example of the sort of routine that saves the developer from a bit of boredom and speeds up development, but isn’t a huge deal. It would be ideal in a collection of routines. I use a custom collection in AceText to do this but a collection of useful developer routines like these would be handy in SQL Prompt. It is handy to have such things that can be quickly pasted into a query window and executed.

 

The post Using sys.dm_exec_describe_first_result_set_for_object. The Hows, Whys and Wherefores. appeared first on Simple Talk.

from Simple Talk https://ift.tt/2OZko4w
via