Table names when migrating MySQL to Microsoft SQL Server

Question

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

SQL Server

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

Answer 1

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.