Is it possible to SHOW CREATE TABLE including the schema name?

user_759866 0
Viewed 65

I can use SHOW CREATE TABLE to display the SQL necessary to create the given table:

mysql> SHOW CREATE TABLE acme.User;

CREATE TABLE `User` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  ...

Is it possible to get the schema name in the output as well? Like:

CREATE TABLE `acme`.`User` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  ...

I tried running the query from another schema, but the output is the same.

mysql
1 Answers

It looks like there is no way to do so.

Sure, we can programmatically add the schema name after CREATE TABLE, but there is more to it: foreign keys.

If the table contains foreign keys, the output does not contain the schema of the target table either, unless the target table is not in the same schema as the base table.

In any case, the current schema when SHOW CREATE TABLE is executed does not matter.

I've written some code to handle the schema name here.

user_7598660
Related