{"id":472,"date":"2010-02-08T14:26:19","date_gmt":"2010-02-08T14:26:19","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=472"},"modified":"2023-09-09T16:44:25","modified_gmt":"2023-09-09T16:44:25","slug":"stored-procedure-to-add-remove-prefix-by-rename-table-mysql","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/stored-procedure-to-add-remove-prefix-by-rename-table-mysql","title":{"rendered":"How to bulk rename table MySQL"},"content":{"rendered":"\n<p>Are you tired of manually renaming MySQL tables, one by one, especially when dealing with a large number of them? In this guide, we&#8217;ll introduce you to a MySQL stored procedure that simplifies the process of renaming tables, allowing you to effortlessly add or remove prefixes in bulk.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Overview<\/h2>\n\n\n\n<p>A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed him a bit better editor, Editplus. With the support of <a href=\"http:\/\/kedar.nitty-witty.com\/working-with-editplus-text-editor-regular-expression-how-to\" target=\"_blank\" rel=\"noreferrer noopener\">regular expressions in Editplus<\/a> we could parse his contents to prepare an SQL file containing RENAME TABLE commands.<\/p>\n\n\n\n<p>Rename tables in MySQL is not very common administrative task, it can be time-consuming and error-prone when dealing with multiple tables. I wrote this blog to help people wanting to rename multiple MySQL tables or perform bulk rename operations in MySQL. I wrote a custom <a href=\"http:\/\/kedar.nitty-witty.com\/?s=stored+procedure\" target=\"_blank\" rel=\"noopener\" title=\"\">MySQL stored procedure<\/a> that provides a practical solution by automating rename of a MySQL table to add or remove prefixes. Although the solution is case specific, it should help you. In case you need it to be tweaked for your need, leave me a comment.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Rename Table<\/strong><\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Adding Prefixes:<\/strong> If you need to add a prefix to table names, this option has you covered. It can be particularly useful for organizing tables or distinguishing between different data sets.<\/li>\n\n\n\n<li><strong>Removing Prefixes:<\/strong> On the other hand, if you wish to clean up your table names by removing existing prefixes, this function will do the job efficiently.<br>For example, clientA_table* to be renamed to clientB_table* <\/li>\n<\/ol>\n\n\n\n<p><strong>Download the Procedure to rename MySQL tables: <\/strong><a href=\"http:\/\/kedar.nitty-witty.com\/wp-content\/uploads\/2010\/02\/prefix_all.sql_.txt\"><strong>prefix_all<\/strong><\/a><\/p>\n\n\n\n<p>Steps to utilize my MySQL stored procedure:<\/p>\n\n\n\n<p><strong>Step 1:<\/strong> Execute or Create the Stored Procedure<\/p>\n\n\n\n<p>You can either download the SQL script containing the stored procedure or copy and paste it into your MySQL environment.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>wget http:\/\/kedar.nitty-witty.com\/wp-content\/uploads\/2010\/02\/prefix_all.sql_.txt\nmysql test &lt; prefix_all.sql_.txt<\/code><\/pre>\n\n\n\n<p><strong>Step 2:<\/strong> Execute the Procedure<\/p>\n\n\n\n<p>Call the stored procedure with the following parameters:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>in_db<\/code> (VARCHAR): Specify the name of the target database.<\/li>\n\n\n\n<li><code>in_prefix<\/code> (VARCHAR): Define the prefix you want to add or remove.<\/li>\n\n\n\n<li><code>in_add_rem<\/code> (TINYINT): Use <code>1<\/code> to add a prefix or <code>0<\/code> to remove it.<\/li>\n<\/ul>\n\n\n\n<p><strong>Example:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CALL prefix_all('YOUR_DATABASE_NAME', 'PREFIX', 1);<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Rename MySQL Tables in bulk<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\">DELIMITER $\n\nDROP PROCEDURE IF EXISTS `prefix_all` $\nCREATE PROCEDURE `prefix_all` (in_db varchar(20),in_prefix varchar(10),in_add_rem TINYINT(1))\nBEGIN\n\nDECLARE done INT default 0;\nDECLARE tbl_nm VARCHAR(30);\nDECLARE ren VARCHAR(200);\n\nDECLARE table_cur CURSOR FOR select table_name from information_schema.tables where table_schema=in_db;\n\nDECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;\nOPEN table_cur;\n  rename_loop:LOOP\n    FETCH table_cur INTO tbl_nm;\n    IF done=1 THEN\n      LEAVE rename_loop;\n    END IF;\n    if in_add_rem=1 then #ADD\n      SET @ren = concat(\"rename table \", in_db,'.',tbl_nm ,\" to \",in_db,'.',in_prefix,tbl_nm,\";\");\n    else\n      set @ren= concat(\"rename table \", in_db,'.',tbl_nm ,\" to \",in_db,'.',right(tbl_nm,length(tbl_nm)-length(in_prefix)),';');\n    end if;\n#    select @ren;\n    prepare ren from @ren;\n    execute ren;\n  END LOOP;\nCLOSE table_cur;\nselect table_name 'Tables' from information_schema.tables where table_schema=in_db;\n\nEND $\n\nDELIMITER ;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<p>Simplify your MySQL table management tasks by using our custom stored procedure. Whether you need to add or remove prefixes from table names, this efficient solution will save you time and effort.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"Are you tired of manually renaming MySQL tables, one by one, especially when dealing with a large number of them? In this guide, we&#8217;ll introduce you to a MySQL stored&hellip;\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[693,427,683,695,692,694,108,426],"class_list":{"0":"post-472","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-scripts-mysql","8":"tag-bulk-rename-mysql","9":"tag-mysql","10":"tag-mysql-rename-table","11":"tag-rename-multiple-tables","12":"tag-rename-table-mysql","13":"tag-rename-table-statement","14":"tag-stored-procedure","15":"tag-technical"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/472","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=472"}],"version-history":[{"count":5,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions"}],"predecessor-version":[{"id":3041,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions\/3041"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=472"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=472"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}