3

I've just migrated a MySQL database over to Microsoft SQL Server using Microsoft SQL Server Migration Assistant for MySQL. For some reason the table names in SQL Server are not named the same way as in MySQL.

If my table is named accounts in MySQL, if will after the migration be called databaseName.accounts, why is that? It makes it pretty annoying since I have to change the table names in all my queries to database.OldDatabaseName.Table instead of just database.Table.

MySQL

enter image description here

SQL Server

enter image description here

Or can I somehow rename the tables? Because when I click rename I can only edit the last part: 'accounts'.

1
  • Are you sure that in your example testreportingdebug isn't in fact a schema on MySQL? Feb 9, 2022 at 13:28

1 Answer 1

3

This is because the two database engines are designed this way:

  • Microsoft SQL Server: There is a SQL Server Instance --> which contains multiple databases --> each database contains schemas
  • MySQL: There is a MySQL database --> which contains multiple schemas

In MySQL the schemas are the databases.

Apparently as suggested by Brendan McCaffrey there might be a way out:

  1. In MySQL Metadata Explorer, select Schemas. The Schema Mapping tab is also available when you select individual schemas. The list in the Schema Mapping tab is customized for the selected object.
  2. In the right pane, click the Schema Mapping tab. You will see a list of all MySQL schemas, followed by a target value. This target is denoted in a two part notation (database.schema) in SQL Server or SQL Azure where your objects and data will be migrated.
  3. Select the row that contains the mapping that you want to change, and then click Modify. In the Choose Target Schema dialog box, you may browse for available target database and schema or type the database and schema name in the textbox in a two part notation (database.schema) and then click OK.
  4. The target changes on the Schema Mapping tab.
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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