How to generate documentation from stored procedures? [closed]

Question

I'm working with a new set of tables and an increasing list of stored procedures.

Is there a way to generate documentation out of the comments on tables and stored procedures, like autodoc or doxygen?

Note: I'm using mysql.

Answer 1

I can't for the life of me understand why this question received a flag for being off-topic. I think it is interesting and very relevant for the working DBA.

Unfortunately, as far as I can ascertain, it is impossible to do this automatically. You can run two commands

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

and

SHOW PROCEDURE STATUS; -- will give you all procedures available.

SHOW CREATE PROCEDURE procedure_name; -- will give you details about a given procedure.

A good discussion of the topic is available here which shows the problems associated with the various commands.

Extending slightly the example given for the sample alluser() procedure as shown in the link above. I added comments with # and --.

DELIMITER //

CREATE PROCEDURE alluser()

# a proc to show all users
BEGIN
   SELECT *  -- selecting from the users' table
   FROM users;
END //
DELIMITER ;

What I found was that the comments are NOT returned using either SHOW PROCEDURE STATUS or SHOW CREATE PROCEDURE alluser; NOR the SQL statement above returns the comments.

[EDIT - I have further found that comments are not returned with SELECT * FROM Information_Schema.Routines.

I searched the system and found the field ROUTINE_COMMENT in INFORMATION_SCHEMA.ROUTINES - but it didn't return the comments either. What this field is for, I don't quite know - maybe it's not implemented yet?

I would respectfully suggest your best bet is to document as you go - put "full documentation for the procedure" as part of your testing and UAT framework.

Answer 2

To my knowledge, there are no tools like doxygen that can make doc from parsing SQL annotated code.

About keeping comments, note that comments in a stored procedure can be saved with the procedure code.

When a CREATE PROCEDURE statement is sent from the mysql command line client to the server, make sure the mysql client is using the -comments option.

See http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html#option_mysql_comments

That being said, SQL under source control is probably better compared to extracting the stored procedures code back from the database.

Answer 3

Eventually I found a starting point thanks to Verace. But comments can be long, therefore i post this as an answer:

DROP PROCEDURE IF EXISTS foobar;

DELIMITER //

CREATE PROCEDURE foobar()

COMMENT '
This procedure does some useful stuff
'
BEGIN
   -- Do something useful here
   SELECT 1;
END //
DELIMITER ;

SELECT ROUTINE_COMMENT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'foobar';
SHOW PROCEDURE STATUS WHERE Name='foobar';
SHOW CREATE PROCEDURE foobar;

I cannot retrieve inline comments, but I can retrieve the comments from the characteristics section.