Menu
Hi I am using mysql 5.0.x
How do I rename a schema?
723
To change the schema of a table by using SQL Server Management Studio, in Object Explorer, right-click on the table and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema. ALTER SCHEMA uses a schema level lock.
In MySQL Workbench, click on the tab you want to rename. Go to file > Save Script As. A window will open that gives you a blank space to type in the name you want. Click Save.
Right-click on the tables which are on the wrong schema, and select "Copy SQL to clipboard". Paste the script in a new SQL window. Repeat for each table you want to migrate. Edit the script to change the schema name.
If you are using SQL Server, you can set the database to single-user mode to close any open connections and prevent other users from connecting while you are changing the database name. In Object Explorer, expand Databases, right-click the database to rename, and then select Rename.
Don't use RENAME DATABASE!!!
Early 5.1 versions had this command but it's been removed since it can corrupt data (reference).
The only way at present is
mysqladmin create new_db_name mysqldump db_name | mysql new_db_name as referred to here
Edit: Obviously this answer will become dated once this gets fixed.
136
It's easy. Export the database to file, then import it again in workbench you can specify the name of the db there.
In workbench go to the Server tab, select Data Export. Select the DB you want to rename, select export to self contained file, and give the file a name. make sure you have Dump structure and Data selected. Hit start export.
In workbench go to the Server tab, select Data Import. Select Import from self contained file. Select the file you created. In the section titled Default Schema to be imported to click the new option. Enter the new name for the DB, then select it from the drop down. Hit Start Import.
Voila, new database with the name you want plus all the tables and data from the old one.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
