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 only takes a minute to sign up.
Sign up to join this community
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
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
WHERE TABLE_SCHEMA = "your_schema_goes_here" out of the command.
– Gwyneth Llewelyn
Nov 2 '16 at 10:53
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.
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 [?] .....'
