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 …
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
Load comments