4.5.7.8 SHOW CREATE TABLE

Shows a CREATE TABLE statement that will create the given table:

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE will quote table and column names according to SQL_QUOTE_SHOW_CREATE option. section 5.5.6 SET Syntax.

User Comments

This function does not seems to be supported in
version 3.22.32... upgrading to the last version
will do the trick I guess.

it would seem that the create table SQL statement outputted by this command is not actually valid under mysql SQL syntax. It contains single quotes (ie " ' ") around the column names and table name. I did a "show create table" on an existing table so I could see what an example. When I dropped the table and use the exact same create table statement, mysql kept complaining that there was an "error in the SQL statement" (cryptic enough to be a microsoft error message). After a few dents in the wall from banging my head against it, I finally figured out that the single quotes were the problem. I removed them and the table was created. There are a few annoyances like this in mysql but on the whole I like the fact that it has a command line interface.

From phpMyAdmin FAQ [1.23] on http://www.phpmyadmin.net/documentation/#faq:

I'm running MySQL on a Win32 machine. Each time I create a new table the table and field names are changed to lowercase!

This happens because the MySQL directive lower_case_table_names defaults to 1 (ON) in the Win32 version of MySQL. You can change this behavior by simply changing the directive to 0 (OFF):

Just edit your my.ini file that should be located in your Windows directory and add the following line to the group [mysqld]:
set-variable = lower_case_table_names=0
Next, save the file and restart the MySQL service. You can always check the value of this directive using the query
SHOW VARIABLES LIKE 'lower_case_table_names';

I believe the problem rsmetrics.com was having has to do with single quotes (') versus backticks (`). The SHOW CREATE command uses backticks to delimit the identifiers, as described in manual section 6.1.2 (http://www.mysql.com/doc/en/Legal_names.html).

Add your own comment.