How do I show the schema of a table in a MySQL database?

From the MySQL console, what command displays the schema of any given table?

5 Answers

For formatted output:

describe [db_name.]table_name; 

For an SQL statement that can be used to create a table:

show create table [db_name.]table_name; 

SHOW CREATE TABLE yourTable; 

or

SHOW COLUMNS FROM yourTable; 

You can also use shorthand for describe as desc for table description.

desc [db_name.]table_name;

or

use db_name;
desc table_name;

You can also use explain for table description.

explain [db_name.]table_name;

See official doc

Will give output like:

+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(10) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(10) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | sal | int(10) | YES | | NULL | | | location | varchar(20) | YES | | Pune | | +----------+-------------+------+-----+---------+-------+ 

Perhaps the question needs to be slightly more precise here about what is required because it can be read it two different ways. i.e.

1. How do I get the structure/definition for a table in mysql?
2. How do I get the name of the schema/database this table resides in?

Given the accepted answer, the OP clearly intended it to be interpreted the first way. For anybody reading the question the other way try

SELECT `table_schema` FROM `information_schema`.`tables` WHERE `table_name` = 'whatever'; 

SELECT COLUMN_NAME, TABLE_NAME,table_schema FROM INFORMATION_SCHEMA.COLUMNS;