{"id":86413,"date":"2020-02-17T16:11:04","date_gmt":"2020-02-17T16:11:04","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86413"},"modified":"2020-02-17T16:11:04","modified_gmt":"2020-02-17T16:11:04","slug":"using-sys-dm_exec_describe_first_result_set_for_object-the-hows-whys-and-wherefores","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/using-sys-dm_exec_describe_first_result_set_for_object-the-hows-whys-and-wherefores\/","title":{"rendered":"Using sys.dm_exec_describe_first_result_set_for_object. The Hows, Whys and Wherefores."},"content":{"rendered":"

I\u2019ve been working on a project unkindly nicknamed \u2018The Gloop\u2019 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\u2019d rather neglected the stored procedures because there was no metadata that could produce the first result set produced by a procedure or trigger.<\/p>\n

I\u2019d been silly because there is, of course, an Execution system DMF that does it: sys.dm_exec_describe_first_result_set_for_object().<\/strong> 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\u00a0sys.dm_exec_describe_first_result_set<\/a>.<\/p>\n

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 \u2026<\/p>\n

INSERT INTO #OurBillOfMaterials EXECUTE dbo.uspGetBillOfMaterials;<\/pre>\n
\u2026 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.<\/p>\n
CREATE TABLE #OurBillOfMaterials\r\n  (\r\n  ProductAssemblyID INT NULL,\r\n  ComponentID INT NULL,\r\n  ComponentDesc NVARCHAR(50) NULL,\r\n  TotalQuantity DECIMAL(38, 2) NULL,\r\n  StandardCost MONEY NULL,\r\n  ListPrice MONEY NULL,\r\n  BOMLevel SMALLINT NULL,\r\n  RecursionLevel INT NULL\r\n  );\r\n\r\nINSERT INTO #OurBillOfMaterials EXECUTE dbo.uspGetBillOfMaterials 800,\r\n'9\/1\/2014';<\/pre>\n
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<\/p>\n
This works on SQL Server 2017 or upwards<\/p>\n
SELECT Object_Schema_Name(p.object_id)+'.'+p.name AS name,\r\n  String_Agg(\r\n     r.name + ' ' + system_type_name + ' '\r\n       + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'\r\n       + CASE WHEN collation_name IS NULL  \r\n\t       OR collation_name = DatabasePropertyEx(Db_Name(), 'Collation')\r\n\t     THEN     '' ELSE ' COLLATE ' + collation_name END,\r\n     ', '\r\n     ) WITHIN GROUP ( ORDER BY column_ordinal asc ) AS result\r\n  FROM sys.procedures AS p\r\n    OUTER APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r\r\n  WHERE r.is_hidden = 0 AND error_state IS NULL \r\n  GROUP BY Object_Schema_Name(p.object_id)+'.'+p.name\r\n  HAVING Object_Schema_Name(p.object_id)+'.'+p.name='dbo.uspGetBillOfMaterials' \r\n<\/pre>\n
If you want a list of all your procedures done, just scrub that last line<\/p>\n
<\/p>\n
For earlier versions such as 2016, try this instead.<\/p>\n
SELECT Name, result from\r\n\t(SELECT \r\n\t\tObject_Schema_Name(p.object_id)+'.'+p.name AS Name,\r\n\t\tstuff((SELECT  ', '+ r.name + ' ' + system_type_name + ' '\r\n       + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'\r\n       + CASE WHEN collation_name IS NULL  \r\n\t       OR collation_name = DatabasePropertyEx(Db_Name(), 'Collation')\r\n\t     THEN     '' ELSE ' COLLATE ' + collation_name END\r\n     FROM sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r\r\n\t WHERE r.is_hidden = 0 AND error_state IS NULL\r\n\t ORDER BY column_ordinal\r\n     FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,2,'') result\r\n  FROM sys.procedures AS p)f(name, result)\r\n  WHERE result IS NOT NULL\r\n  AND name='dbo.uspGetBillOfMaterials' \r\n<\/pre>\n
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.<\/p>\n
This is an example of the sort of routine that saves the developer from a bit of boredom and speeds up development, but isn\u2019t 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.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019ve been working on a project unkindly nicknamed \u2018The Gloop\u2019 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\u2019d rather neglected……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-86413","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86413","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=86413"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86413\/revisions"}],"predecessor-version":[{"id":86417,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86413\/revisions\/86417"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=86413"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=86413"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=86413"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=86413"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}