hosted by liquidweb

Go Back   Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : How to alter all tables in MYSQL with only one command

Forum Jump

How to alter all tables in MYSQL with only one command

Reply Post New Thread In Programming Discussion Subscription
Send news tip View All Posts Thread Tools Search this Thread Display Modes
WHT Addict
Join Date: Oct 2007
Posts: 129

How to alter all tables in MYSQL with only one command

Tried and tried could not find the command
I have this

ALTER TABLE table_name_goes_here CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

but if I use it I have to do with all tables
how can I use the above command in a way to change all tables
in one particular database

Sponsored Links
Community Guide
Join Date: Jan 2006
Location: Athens, Greece
Posts: 1,479
PHP it.

$tbls = array('t1','t2','t3','t4');
foreach ($tbls as $t)
$query = "ALTER ".$t."CONVERT ..... ";
$res = mysql_query($query);
You need the ALTER privilege for that.


Web Hosting Master
Join Date: Aug 2003
Location: California, USA
Posts: 581
PHP Code:
// your connection

// convert code
$res mysql_query("SHOW TABLES");
while (
$row mysql_fetch_array($res))
    foreach (
$row as $key => $table)
mysql_query("ALTER TABLE " $table " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
$key " =&gt; " $table " CONVERTED<br />";
Tested. Works. Depending on how many tables are in the database it can take a few moments. My database had 76 tables and it took about 20 seconds.

Last edited by etogre; 03-28-2008 at 02:54 PM.
Sponsored Links
WHT Addict
Join Date: Oct 2007
Posts: 129
thanks a lot

what should I put here is this correct?
so I make a php and run it in my browser?

Web Hosting Master
Join Date: Aug 2003
Location: California, USA
Posts: 581
yep, just edit that part like you did, stick it in a PHP file and then run it.

You will recieve output like this, and you will know it has run fine:

0 => tablename CONVERTED
Tables_in_dbname => tablename CONVERTED
0 => table2 CONVERTED
Tables_in_dbname => table2 CONVERTED

Last edited by etogre; 03-29-2008 at 01:20 AM.
New Member
Join Date: Nov 2009
Posts: 1

Just wanted to say THANKS!!!! I lost almost 10 hours trying to solve a problem with UTF8 in my MySQL base ... and you definately saved a lot of my nerves!

Thanks again!!!

Be fine!

Web Hosting Master
Join Date: May 2009
Posts: 766
FYI...provided you have the PHP CLI should just be able to do this on the command line:

# php myScript.php

New Member
Join Date: Apr 2013
Posts: 1
Question Use HeidiSQL

You can use heidisql. it has a bulk table editor. u can alter all table at a time. heidisql is totally free.... thnx


Related posts from
Title Type Date Posted
Docker Buys Kitematic, a Startup Focused on Making it Easy to Run Docker on Macs Web Hosting News 2015-03-12 09:10:10
CloudLinux Releases Software for Web Hosts to Set Limits on Accounts Running MySQL Web Hosting News 2015-01-26 14:42:26
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Linode Releases CLI Tool for Cloud Platform Web Hosting News 2014-01-29 17:15:43
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Log in with your username and password

Forgot Password?
WHT Host Brief Email:

We respect your privacy. We will never sell, rent, or give away your address to any outside party, ever.

Web Hosting News:
WHT Membership
WHT Membership



Welcome to

Create your username to jump into the discussion! is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.

(4 digit year)

Already a member?