Document the output of all the SPs in HTML fashion

,

Starting from version 2012, SQL server provides us with a number of system stored procedure we can use to explore and document our databases.

One of these system SPs is sys.dm_exec_describe_first_result_set_for_object (https://msdn.microsoft.com/en-us/library/ff878236.aspx), that describes the result of a given object id (usually a custom stored procedure). The SP just describes the output columns, and does not take into account any logic embedded into the inspected SP. To understand what the custom SP does, you have to open and inspect the SP by yourself - or ask to the SP's author.

Anyway, we can use sys.dm_exec_describe_first_result_set_for_object to complete our database documentation. 

According to MSDN, we can extract data with this simple query:

SELECT p.name, r.*

FROM sys.procedures AS p

CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

GO

If you run the query, you'll soon understand that you can't use it in a document, unless you do a heavy manual rearrangement.

So I prepared a script that rearranges the extracted data in a HTML fashion. It will be easy to add a handful of lines to save the content of the output to a file or to send it via email.

-- first of all, declare the db to use

USE [myDb]
GO

-- now, we can extract the data; the info may be extracted using this simple query
--    SELECT p.name, r.*
--    FROM sys.procedures AS p
--    CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;
--    GO
-- but it could be not so easy to read; so let's create a structure to hold data in a HTML fashion

-- first, declare the output table
DECLARE @Results TABLE
(
   pName   VARCHAR(MAX),   --   the name of the sp
   iLevel  SMALLINT,       --   the level of the info
   cOrder  SMALLINT,       --   the column order
   cName   VARCHAR(MAX),   --   the column name
   tName   VARCHAR(MAX),   --   the column type, extended
   html    VARCHAR(MAX)    --   the content of the row in a HTML fashion
);

-- then read the data and create the html output, working with levels
-- 1. separator
-- 2. sp name
-- 3. table header
-- 4. table body
-- 5. table closing

-- level 1, to separate sp/table outputs
INSERT INTO @Results
SELECT 
   p.name, '1' AS iLevel, 0 AS cOrder, '' AS cName, '' AS tName
   , '<br />' AS html
FROM 
   sys.procedures AS p

-- level 2, shows the sp name
INSERT INTO @Results
SELECT 
   p.name, '2' AS iLevel, 0 AS cOrder, p.name AS cName, '' AS tName
   , '<h2>'+p.name+'</h2>' AS html
FROM 
   sys.procedures AS p

-- level 3, the heading of the sp/table
INSERT INTO @Results
SELECT 
   p.name, '3' AS iLevel, 0 AS cOrder, REPLICATE('-',60) AS cName, '' AS tName
   , '<table><tr><th>Column Name</th><th>Column Type</th></tr>' AS html
FROM 
   sys.procedures AS p

-- level 4, shows the sp output columns
INSERT INTO @Results
SELECT 
   p.name, '4' AS iLevel, r.column_ordinal, ISNULL(r.name,''), ISNULL(r.system_type_name,'')
   , '<tr><td>'+r.name+'</td><td>'+r.system_type_name+'</td></tr>' AS html
FROM 
   sys.procedures AS p
   CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, NULL) AS r

-- level 5, the closing of the sp/table
INSERT INTO @Results
SELECT 
   p.name, '5' AS iLevel, 0 AS cOrder, '' AS cName, '' AS tName
   , '</table>' AS html
FROM 
   sys.procedures AS p

-- ---------------------------------------
-- now we can inspect the result table (without the html-formatted content)
SELECT    cName,tName
FROM      @Results
ORDER BY  pName, iLevel, cOrder


-- ----------------------------------
-- build the output

   -- declare the output var
   DECLARE @tableHTML  NVARCHAR(MAX) ;

   -- select the whole content in one single var
   SELECT @tableHTML = COALESCE(@tableHTML + '','') + ISNULL(html,'') FROM @Results ORDER BY pName, iLevel, cOrder

   -- add some formatting elements, you can add your preferred formatting!
   SET @tableHTML =
      N'<H1>First Result Set for Stored Procedures</H1>' +
      N'<p>Information gathered through sys.dm_exec_describe_first_result_set_for_object</p>' +
      N'<style>' +
      N'table, th, td {' +
      N'   border: 1px solid black;' +
      N'   border-collapse: collapse;' +
      N'   border-spacing: 2px;' +
      N'   border-color: gray;' +
      N'}' +
      N'</style>' +
      @tableHTML

   -- print the content
   PRINT @tableHTML

   -- remember that the output in SSMS is limited to 4000 characters, 
   -- so you may want to save the content to a file or send it via email

Rate

4.17 (6)

Share

Share

Rate

4.17 (6)