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 
