14

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

18

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

| improve this answer | |
  • No there is not a way to do what you want in a single command. – World Wide DBA Feb 22 '13 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. – Gwyneth Llewelyn Nov 2 '16 at 10:53
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.

| improve this answer | |
  • 1
    Why is this the easiest way? What if you cannot bring down your DB to modify the character set? – dezso Mar 17 '17 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. – Andrew Smith Mar 20 '17 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 Oct 22 '18 at 18:51
1

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 [?] .....'
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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