I am currently looking at automating my SQL Server documentation. In particular documentation for stored procedures.
I can get the list of input parameters from the sys.parameters
but I'm stuck on the output.
As a simplification the contents of a stored procedure will return multiple tables such as:
CREATE PROCEDURE usp_MyProc
AS
BEGIN
SELECT
Column1, Column2, Column3
FROM
Tablename1
SELECT
Column1, Column2, Column3
FROM
Tablename2
END
What I'm looking for would be a script to give me the definition of the output tables. e.g.
Table 1
1 Column1 NVARCHAR(MAX)
2 Column2 DATETIME
3 Column3 BIT
Table 2
1 Column1 UNIQUEIDENTIFIER
2 Column2 VARCHAR(20)
3 Column3 INT
Any ideas how I could access this data via a script? I can of course go in manually, trace the to the original table, etc. but that's a manual process. I want to do this via a script to generate the definition documentation.
PS: Yeah I know the documentation should come first!