22

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

hodl
  • 321
  • 1
  • 2
  • 6

3 Answers3

27

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

András Váczi
  • 31,778
  • 13
  • 102
  • 151
Mr.Brownstone
  • 13,242
  • 4
  • 38
  • 55
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.

Andrew Smith
  • 31
  • 1
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 [?] .....'
Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116