MySQL: Can stored procedure information be extracted from the information schema?

Question

Is it possible to retrieve stored procedure information like name, parameter nane/position/type from the Information Schema in MySQL or possible in some other way?

Answer 1

You can get a lot of information, including the name, from INFORMATION_SCHEMA.ROUTINES. See the MySQL Manual for details.

The parameter information would be contained in the INFORMATION_SCHEMA.PARAMETERS table, but this table is not available in MySQL, so I'm not sure that you can get that information from INFORMATION_SCHEMA.

If you just need the argument list, you can select the procedure names from INFORMATION_SCHEMA.ROUTINES then get the CREATE PROCEDURE statements for each of them using SHOW CREATE PROCEDURE. If you just need the arguments and types, you should be able to parse them without too much trouble.

Answer 2

I believe something like what you are looking for is possible using MySQL's built in backup program: mysqldump. Give this a try:

mysqldump --routines --triggers --nodata --skip-opt YOURDB

Details: A quick look at the documentation (MySQL 5.4) shows you definitely want to run it with the --routines option, as well as probably --triggers. I'm guessing you'll want to skip most of the data in this dump, just to look at the procedures, in which case you may want the options --nodata --skip-opt and probably some other options I haven't identified yet. Check out the documentation for your version for whatever other options you want. Also note you can specify just some of the tables in the DB by adding their names on the end.