CODEWITHSUNDEEP
mysqldatabaseschemadatabase-schema
dlamblin
dlamblin

Reputation: 45321

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?

Upvotes: 475

Views: 684731

Answers (5)

Bobby
Bobby

Reputation: 11576

SHOW CREATE TABLE

The SHOW CREATE TABLE statement can be used to retrieve the CREATE TABLE statement to reproduce this table.

For example:

SHOW CREATE TABLE yourTable;

Or with a prefixed database name:

SHOW CREATE TABLE yourDatabase.yourTable;

This will produce a single row with to columns, the table name and the CREATE STATEMENT. For example:

       Table: yourTable
Create Table: CREATE TABLE `yourTable` (
  `id` int NOT NULL AUTO_INCREMENT,
  `s` char(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SHOW COLUMNS FROM

Alternatively the SHOW COLUMNS FROM statement can be used to produce a list of all the columns in the given table.

For example:

SHOW COLUMNS FROM yourTable;

Or with a prefixed database name:

SHOW COLUMNS FROM yourDatabase.yourTable;

The output is a simple table with all the columns. For example:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

DESCRIBE/EXPLAIN

There is also the DESCRIBE/EXPLAIN statement which also lists the columns of the given table.

Upvotes: 126

Omry Yadan
Omry Yadan

Reputation: 33646

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;

Upvotes: 691

Paul Campbell
Paul Campbell

Reputation: 1986

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

Upvotes: 22

Lam
Lam

Reputation: 436

SELECT COLUMN_NAME, TABLE_NAME,table_schema
FROM INFORMATION_SCHEMA.COLUMNS;

Upvotes: 8

Somnath Muluk
Somnath Muluk

Reputation: 57656

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

Upvotes: 23

Related Questions