How do I show the schema of a table in a MySQL database?
MysqlDatabaseSchemaDatabase SchemaMysql Problem Overview
From the MySQL console, what command displays the schema of any given table?
Mysql Solutions
Solution 1 - Mysql
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;
Solution 2 - Mysql
SHOW CREATE TABLE yourTable;
or
SHOW COLUMNS FROM yourTable;
Solution 3 - Mysql
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 | |
+----------+-------------+------+-----+---------+-------+
Solution 4 - Mysql
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.
- How do I get the structure/definition for a table in mysql?
- 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';
Solution 5 - Mysql
SELECT COLUMN_NAME, TABLE_NAME,table_schema
FROM INFORMATION_SCHEMA.COLUMNS;