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 …

… 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.

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

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

For earlier versions such as 2016, try this instead.

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.