Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

export just procedures from a mysql database using mysqldump

 
James.HD Li
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guys

I'm looking for a way to dump all the procedures from a mysql database with mysqldump. Just procedures, no tables.

Currently, I can export all the procedures with --routine option, but I just can not figure out how to remove those 'table related' statement. If anyone knows, please help.

James
 
Peter Johnson
author
Bartender
Posts: 5852
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You would have better luck asking in the JDBC forum, since that is where database-related questions go. Moving.
 
Campbell Ritchie
Sheriff
Posts: 48910
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Am I right in thinking that all procedures start with CREATE PROCEDURE and end with ; ? If you do an SQL dump, you get the SQL for the entire database in a plain text file. You can easily scan it for its text, and it shouldn't be hard to create a regular expression to find procedures. Finding the end of a procedure might be more awkward because you might have DELIMITER instructions so there are ; in the text. You might also have ; inside 'strings' with 'quote marks' like this: 'Campbell Ritchie should never be trusted; he makes mistakes all the time.' You can probably permit quotes and DELIMITER instructions in your regular expression. You never know, there might be one ready-made somewhere. If you are looking for one, try here.
 
James.HD Li
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter, thanks for take this topic to a better place.

Campbell, thank you so much. This is quite a professional way and should be smart enough to resolve this problem.

But I just wander if there is a simple way like a command line that just dump all the procedures?

J
 
Campbell Ritchie
Sheriff
Posts: 48910
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome. try the MySQL manual; if you find "mysqldump" in the index (here for MySQL5.0) or look for newer versions, there appears to be a routines option which you may find very helpful.
 
James.HD Li
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I have too name a table after the database name and manually delete that 'create table script'
Thanks a lot for you reply.
 
Campbell Ritchie
Sheriff
Posts: 48910
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic