1

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • I'm afraid you'll only find the procedure's implicit `INT` return type in system catalog. As for getting the info from source code, in any but the most trivial cases you'll need to do complete parsing and semantic analysis of the script - any simple tool /script won't help with `SELECT *` etc. and may even have hard time to properly recognize `SELECT` from `INSERT ... SELECT`. If yours is a larger project and you're looking for commercial solutions as well, the company I work for [has a SQL parser and analyzer that could help you](https://getmanta.com/). – Jiri Tousek Jan 27 '17 at 09:54
  • 3
    For SQL Server **2012** and newer, [have a look at the `sp_describe_first_result_set` system stored procedure](https://msdn.microsoft.com/en-us/library/ff878602.aspx) that should provide those answers you're looking for - at least for the first result set – marc_s Jan 27 '17 at 10:08
  • Hi Marc - Great answer sent me of in a good direction. Only problem now is how do I read it from the second table? I've been googling around and not found anything yet. The clue here is in the name 'first_result_set'. a big +1 from me for the help so far. – Matthew Baker Jan 27 '17 at 11:16

0 Answers0