20

Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database:

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8;

My objective is to modify the charset from latin1 to utf8 to all tables.

UPDATE: RDBMS is MySQL

3 Answers 3

25

No, there is no such command. But what you can do is write a quick query to generate the SQL for you like so:

USE INFORMATION_SCHEMA;
SELECT 
CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET UTF8;") 
AS MySQLCMD FROM TABLES 
WHERE TABLE_SCHEMA = "your_schema_goes_here";

Then you can run the output from this to do what you need.

Sources:

http://forums.mysql.com/read.php?20,244395,244421#msg-244421

3
  • No there is not a way to do what you want in a single command. Commented Feb 22, 2013 at 11:42
  • To get this to work under MariaDB, for some reason, I had to leave the WHERE TABLE_SCHEMA = "your_schema_goes_here" out of the command. Commented Nov 2, 2016 at 10:53
  • 1
    For future Googlers: This SQL code works (including the WHERE) on on MariaDB. Also for the SQL newbs (me included), a table schema means the database name. So in the WHERE clause, just specify the database name you wish to get all tables for. This works perfectly for those that have hundreds of databases, with hundreds of tables each, like WordPress for instance.
    – Studocwho
    Commented Jun 7 at 23:00
3

Easiest way: export the database. Open the exported database in a text editor and perform a Find/Replace with "UTF8" or whatever. Re-import amended database.

3
  • 1
    Why is this the easiest way? What if you cannot bring down your DB to modify the character set? Commented Mar 17, 2017 at 14:48
  • Because it requires no SQL knowledge. Obviously, if you cannot export the database: ease and this method would be irrelevant. But, if you have sufficient privilege to edit the database directly, it's highly likely you can export it also. Commented Mar 20, 2017 at 10:27
  • This was the best solution in my case! quick fix that came pretty handy. I was working in my local when I started having an "illegal mix of collations" error in my database for some reason. +1.
    – evl183
    Commented Oct 22, 2018 at 18:51
2

If you are working with MS SQL Server then there is an undocumented stored procedure ms_foreachtable that you can use. Use replace the table name with a ? in the statement.

So in your example

EXEC ms_foreachtable 'ALTER TABLE [?] .....'
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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