I need to create a dump file that contains a single stored procedure from a database, not all routines.
I've tried something like this:
mysqldump -u root -pssmart --skip-triggers --no-create-info --no-data \
--no-create-db --skip-opt test_fshop SP_ITEM_SALES_REPORT -R \
> test_fshop_routines.sql
Here test_fshop
is my DB name and SP_ITEM_SALES
is my SP. I want my SP as .sql file.
Best Answer
BAD NEWS
You cannot use mysqldump to do this.
GOOD NEWS
You can use the mysql client. First, find out if the Stored Procedure exists. If it does, then fetch the type of Procedure it is (
PROCEDURE
orFUNCTION
). Then, issue theSHOW CREATE
for the Stored Procedure. You will have to strip the top 3 and bottom 3 lines:Here is a sample run:
GREAT NEWS
Here is a script that will dump every Stored Procedure into separate SQL files:
Give it a Try !!!