Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

No I don't believe there is. 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

share|improve this answer
    
This answer is useful but I am asking if there is a single or a one line command to modify all tables within a database. +1 –  Mar Cejas Feb 22 '13 at 3:59
    
No there is not a way to do what you want in a single command. –  Mr.Brownstone Feb 22 '13 at 11:42
add comment

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 [?] .....'
share|improve this answer
    
My RDBMS is MySQL –  Mar Cejas Feb 20 '13 at 5:49
    
This article talks about having ms_foreachtable in MYSQL MYSQL CLONE OF SP_MSFOREACHTABLE –  Maryam Arshi Feb 20 '13 at 6:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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