How do I show the schema of a table in a MySQL dat

Question

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

Answer 1

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';

Answer 2

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    |       |
+----------+-------------+------+-----+---------+-------+

Answer 3

describe [db_name.]table_name;

for formatted output, or

show create table [db_name.]table_name;

for the SQL statement that can be used to create a table.

Answer 4

SHOW CREATE TABLE yourTable;

or

SHOW COLUMNS FROM yourTable;

Answer 5

SELECT COLUMN_NAME, TABLE_NAME,table_schema
FROM INFORMATION_SCHEMA.COLUMNS;