Phil Factor dives into sys.dm_exec_describe_first_result_set_for_object():
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.
I’m going to have to try this out, but I have problems with sp_describe_first_result_set and how it blows up if you use a temp table in the stored procedure whose result set you’re grabbing.