Method of documentation for SQL Stored Procedures

Question

I work in a location where a single person is responsible for creating and maintaining all stored procedures for SQL servers, and is the conduit between software developers and the database. There are a lot of stored procedures in place, and with a database diagram it is simple enough 90% of the time to figure out what the stored procedure needs for arguments/returns as output. For the other 10% of the time, however, it would be helpful to have a reference. Since the DBA is a busy guy (aren't we all?), it would be good to have some program which documents the stored procedures to a file so that the developers can see it without being able to access the SPs themselves.

The question is, does anyone know of a good program to accomplish this? Basically what we need is something that gives the name of the SP, the argument list and the output, both with datatypes and a nullable flag.

Answer 1

The information_schema views will have that information. You'll want "Routines" and "Parameters"

http://msdn.microsoft.com/en-us/library/aa933204(v=SQL.80).aspx

These views are available on SQL2000/SQL2005/SQL2008

You mentioned the developers might not have access to the SPs. You may want to script the schema (including SPs) to a file so the developers can install a copy of it. Stored procedure signatures are nice, but unless you have the source code behind the stored procs, you can't see whats being done with the parameters.

Answer 2

I think you could write that kind of utility yourself. Just connect to the DB, get list of all stored procedures and walk through their parameters logging them in any format you like.

Have a look at this question to learn how:

Get a Parameter List from an SQLDataSource given Stored Procedure Name

Answer 3

SHOW PROCEDURE CODE proc_name

this is a console command -primitive - but it does what you want ?