MySQL: Can stored procedure information be retrieved from information schema?
You can get a lot of information, including the name, from INFORMATION_SCHEMA.ROUTINES
. See the MySQL manual for details.
The parameter information will be contained in the table INFORMATION_SCHEMA.PARAMETERS
, but this table is not available in MySQL, so I'm not sure if you can get this information from INFORMATION_SCHEMA
.
If you just want a list of arguments, you can select the procedure names from INFORMATION_SCHEMA.ROUTINES
, then get instructions CREATE PROCEDURE
for each one using SHOW CREATE PROCEDURE
. If you just want arguments and types, you should be able to parse them without too much trouble.
source to share
I believe something like what you are looking for, perhaps using MySQL's built-in backup program: mysqldump. Try:
mysqldump --routines --triggers --nodata --skip-opt YOURDB
Details: A quick look at the documentation (MySQL 5.4) reveals that you definitely want to run it with the option --routines
, as well as probably --triggers
. I am assuming that you will want to skip most of the data in this dump, just look at the procedures, in which case you may need options --nodata --skip-opt
and maybe some other parameters that I have not defined yet. Check out the documentation for your version for any other options you want. Also note that you can only specify some of the tables in the database by appending their names to the end.
source to share