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.
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