Automating SQL Server documentation - stored procedure return table definition

131 Views Asked by At

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!

0

There are 0 best solutions below