• Home
  • Technology
    • Ebullientech
    • Entrepreneurship
    • Video Technology
    • Cloud Computing
    • Business Analysis
  • Thoughts
    • Food
    • General
    • Stories
    • Places
    • Workplace
    • Social Issues
    • Reviews
  • Quotes
  • Ebullientech News and Events
  • About

Kamalika's Notebook

thinking nothing...............!!

You are here: Home / Kamalika / Technology / MySQL / How to rename a stored procedure in MySQL?

How to rename a stored procedure in MySQL?

February 1, 2013 By Kamalika Leave a Comment

how to rename a mysql stored procedureIf you have a situation in hand where you need to rename a STORED PROCEDURE in MySQL and not DROP it then there are 2 ways I could figure out to tackle it. Before I start describing the methods I would like to state why I needed to rename a STORED PROCEDURE in MySQL?

I always have a STORED PROCEDURE named as “x” in all my projects databases where I write temporary code to tackle a particular problem and when the experiment is successful I create a new STORED PROCEDURE and copy & paste code from “x” PROCEDURE. I was following this process for quite some time happily, but then came a situation where it looked time consuming and I needed a faster method!

I found Method 1 by Google-ing and Method 2 was my sudden discovery!

Method 1: Rename a STORED PROCEDURE

SET name = ‘<new_proc_name>’,
specific_name = ‘<new_proc_name>’
WHERE db = ‘<database>’ AND
name = ‘<old_proc_name>’;

So if I want to rename my “x” PROCEDURE to “new_x” the statement would look like the following:

UPDATE `mysql`.`proc`
SET name = ‘new_x’,
specific_name = ‘new_x’
WHERE db = ‘db_mydb’ AND
name = ‘x’;

Please Also note: If you have granted privileges to users for this procedure you will need to update the procedure name in procs_priv as well.

UPDATE `mysql`.`procs_priv`
SET Routine_name = ‘<new_proc_name>’
WHERE Db = ‘<database>’ AND
Routine_name = ‘<old_proc_name>’;

FLUSH PRIVILEGES;

You can execute the above queries from MySQL GUI Tool like MySQL Query Browser or MySQL Command Prompt.

Method 2: Rename a STORED PROCEDURE with a backup

This method is my sudden discovery and I figured it before I found Method 1 by Google-ing and I execute it in MySQL Query Browsers (needless to say it is still my favorite tool to operate MySQL!)

– Select the PROCEDURE “x” and press F2 or right click and select “Edit Procedure”

– PROCEDURE opens in the Query Browser Editor

– Modify DROP PROCEDURE IF EXISTS `db_mydbs`.`x` to DROP PROCEDURE IF EXISTS `db_mydbs`.`new_x`

– Modify CREATE PROCEDURE `db_mydb`.`x` () to CREATE PROCEDURE `db_mydb`.`new_x` ()

– Click to Continue button

The above steps create a new PROCEDURE named “new_x” and also keeps the PROCEDURE named “x” intact.

I generally use Method 2 because it lets me keep my handy “x” PROCEDURE intact and create another PROCEDURE with the same code in a mouse click. Method 2 may not sound very scientific or authentic but since MySQL respond to it quite nicely I guess it is not as bad :).

© Kamalika Guha Roy

Filed Under: MySQL Tagged With: How to rename a stored procedure in MySQL, Learn more about How to Rename a Database in MySQL, MySQL, MySQL STORED PROCEDURE, Rename a MySQL procedure

« How To Deploy Ruby on Rails in Godaddy?
A Few Useful MySQL Commands / Queries for Amateur DBAs – Part 1 »

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Connect with Facebook

Let’s connect…

  • Facebook
  • Instagram
  • LinkedIn
  • Twitter
  • YouTube

Hello There…

I'm Kamalika, a techpreneur & startup mentor, blogger, hobbyist photographer, Netflix & Kindle indulgent, food connoisseur, Starbucks aficionado and former Disney employee ...next

Looking for something?

Tags

All in One SEO Pack AWS Banaras Blogger Blogging Blogs Creating Backup Schedule in RHEL 5 Dasaswamedh Ghat Ebullientech Ebullientech Interactive LLP Facebook Facebook Developers GeoTech GeoTech Informatics HipHop for PHP iPhone iPhone Apps Kamalika Guha Roy Kashi Ganga LAMP MindTwister MySQL Nabaneeta Guha Roy Online Marketing PayPal PayPal's suspension of Indian bank transactions Pentago PHP PHP & MySQL Saraswati Puja Search Engine Optimization SEO The Ganges TypePad UCO HUT UTV Indiagames Varanasi Web 2.0 Web 2.0 Design Web Design Web Marketing Website Design WordPress WordPress.COM WordPress.ORG

Timeline

  • 2020 (7)
  • 2019 (4)
  • 2018 (1)
  • 2015 (6)
  • 2014 (13)
  • 2013 (9)
  • 2010 (7)
  • 2009 (2)

Topics

Copyright © 2021 · Kamalika Guha Roy, Powered by Ebullientech Interactive LLP