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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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";
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.
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.
CREATE PROCEDURE statement is sent from the mysql command line client to the server, make sure the
mysql client is using the
That being said, SQL under source control is probably better compared to extracting the stored procedures code back from the database.
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.