Take the 2-minute tour ×
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's 100% free, no registration required.

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database

share|improve this question
    
Anything you've already tried? What client do you use? Is this a one-off thing or you need it programmatically? –  dezso Feb 6 '13 at 10:59
    
am using mysql. 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 dbname and SP_ITEM_SALES is my sp. I want my SP as .sql file –  BALA Feb 6 '13 at 11:14
    
MySQL administrator tools provides this option. To take backup of a single SP. Click here to get more details –  Praveen Prasannan Apr 8 '13 at 4:41

3 Answers 3

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 or FUNCTION). Then, issue the SHOW CREATE for the Stored Procedure. You will have to strip the top 3 and bottom 3 lines:

DBNAME=${1}
SPNAME=${2}
SPFILE=${DBNAME}_${SPNAME}.sql
SPTEMP=${DBNAME}_${SPNAME}.tmp
MYSQL_CONN="-u... -p..."
SQLSTMT="SELECT COUNT(1) FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
PROC_EXISTS=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
if [ ${PROC_EXISTS} -eq 1 ]
then
    SQLSTMT="SELECT type FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
    PROC_TYPE=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
    SQLSTMT="SHOW CREATE ${PROC_TYPE} ${DBNAME}.${SPNAME}\G"
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}

    #
    # Remove Top 3 Lines
    #
    LINECOUNT=`wc -l < ${SPFILE}`
    (( LINECOUNT -= 3 ))
    tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}

    #
    # Remove Bottom 3 Lines
    #
    LINECOUNT=`wc -l < ${SPTEMP}`
    (( LINECOUNT -= 3 ))
    head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
else
    echo "Stored Procedure ${DBNAME}.${SPNAME} Does Not Exist"
fi
rm -f ${SPTEMP}

Here is a sample run:

[root@***]# ./GetMyProc.sh common DMSPushers
[root@***]# cat common_DMSPushers.sql
CREATE DEFINER=`pma`@`10.%` PROCEDURE `DMSPushers`()
BEGIN

  DECLARE cntr INT DEFAULT 0;
  DECLARE dealerName VARCHAR(50);
  DECLARE dealerID INT;

  DECLARE done boolean DEFAULT false;
  DECLARE dealers CURSOR FOR SELECT bdd.dealer_id, ta.name FROM `b_dealer__dmsaccount` bdd left join t_dmsaccount ta on bdd.dmsaccount_id = ta.id where ta.dms_type = 1 order by bdd.dealer_id desc;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := true;

  OPEN dealers;

  read_loop: LOOP
        FETCH dealers INTO dealerID, dealerName;
        SET cntr = cntr + 1;

    IF done OR cntr > 200 THEN
      LEAVE read_loop;
    END IF;

        IF dealerID > 0 AND dealerID < 664 THEN
                IF dealerName IS NULL THEN
                        SET dealerName = '';
                END IF;
                SET dealerName = REPLACE(dealerName,'''','''''');

                -- Does the Database even exist???
                SET @query = CONCAT('SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ''D',dealerID,''' INTO @schemaExists');
                PREPARE stmt1 FROM @query;
                EXECUTE stmt1;

                IF @schemaExists > 0 THEN
                        SET @query = CONCAT('INSERT dealers_that_push (dealerID, dealerName, pushDate) SELECT ', dealerID, ',''', dealerName ,''',date FROM D',dealerID,'.contactLog where message like ''%pushed to the DMS%''');
                        PREPARE stmt1 FROM @query;
                        EXECUTE stmt1;
                END IF;

        END IF;

  END LOOP;

  CLOSE dealers;
END
[root@***]#

GREAT NEWS

Here is a script that will dump every Stored Procedure into separate SQL files:

MYSQL_CONN="-u... -p..."
SQLSTMT="SELECT COUNT(1) FROM mysql.proc"
PROCCOUNT=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
if [ ${PROCCOUNT} -eq 0 ] ; then exit ; fi
SPLIST=""
for DBSP in `mysql ${MYSQL_CONN} -ANe"SELECT CONCAT(type,'@',db,'.',name) FROM mysql.proc"`
do
    SPLIST="${SPLIST} ${DBSP}"
done
for TYPEDBSP in `echo "${SPLIST}"`
do
    DB=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $2}'`
    SP=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $3}'`
    SQLSTMT=`echo "SHOW CREATE ${TYPEDBSP}\G" | sed 's/@/ /'`
    SPFILE=${DB}_${SP}.sql
    SPTEMP=${DB}_${SP}.tmp
    echo Echoing ${SQLSTMT} into ${SPFILE}
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}
    #
    # Remove Top 3 Lines
    #
    LINECOUNT=`wc -l < ${SPFILE}`
    (( LINECOUNT -= 3 ))
    tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}
    #
    # Remove Bottom 3 Lines
    #
    LINECOUNT=`wc -l < ${SPTEMP}`
    (( LINECOUNT -= 3 ))
    head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
    rm -f ${SPTEMP}
done
ls -l

Give it a Try !!!

share|improve this answer

I got the following from stack overflow (but lost the link)

mysqldump  --compact --no-create-info \
    --where="db='serialize' AND type='PROCEDURE' AND name IN ('sp_abraca_dabra')" \
    --databases mysql --tables proc > outputfile.sql

Change "serialize" and "sp_abraca_dabra" to suit your database and stored procedure respectively.

share|improve this answer

If you want to dump ONLY one procedure from a database, this line should do the trick. It is not the mysqldump utility, but the mysql interactive application itself.

mysql --user=root mydb -e "SHOW CREATE PROCEDURE myprocedure;" > myprocedure.sql

Here you can read my full answer for Windows and batch: http://stackoverflow.com/a/25629708/488117

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.