CODEWITHSUNDEEP
mysql
Jakub Arnold
Jakub Arnold

Reputation: 87260

How to get database structure in MySQL via query?

Is it possible to somehow get structure of MySQL database, or just some table with simple query?

Or is there another way, how can I do it?

Upvotes: 225

Views: 471530

Answers (11)

Bill the Lizard
Bill the Lizard

Reputation: 406035

I think that what you're after is DESCRIBE

DESCRIBE <table name>;

You can also use SHOW TABLES

SHOW TABLES;

to get a list of the tables in your database.

Upvotes: 324

Vasanth Saminathan
Vasanth Saminathan

Reputation: 585

You can pick any one of the command below. All of them are more or less same:

SHOW CREATE TABLE TABLE_NAME;

DESC TABLE_NAME;

SHOW FULL COLUMNS FROM TABLE_NAME; (for column properties)

EXPLAIN TABLE_NAME;

DESCRIBE TABLE_NAME;

Upvotes: 11

Shudipta Sharma
Shudipta Sharma

Reputation: 5882

In the following example,

playground is the database name and equipment is the table name

Another way is using SHOW-COLUMNS:5.5 (available also for 5.5>)

$ mysql -uroot -p<password> -h<host> -P<port> -e \
    "SHOW COLUMNS FROM playground.equipment"

And the output:

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| type  | varchar(50) | YES  |     | NULL    |                |
| quant | int(11)     | YES  |     | NULL    |                |
| color | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

One can also use mysqlshow-client (also available for 5.5>) like following:

$ mysqlshow -uroot -p<password> -h<host> -P<port> \
    playground equipment

And the output:

mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: playground  Table: equipment
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type        | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)     |                   | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| type  | varchar(50) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
| quant | int(11)     |                   | YES  |     |         |                | select,insert,update,references |         |
| color | varchar(25) | latin1_swedish_ci | YES  |     |         |                | select,insert,update,references |         |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

Upvotes: 4

Zenshai
Zenshai

Reputation: 10747

Take a look at the INFORMATION_SCHEMA.TABLES table. It contains metadata about all your tables.

Example:

SELECT * FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE TABLE_NAME LIKE 'table1'

The advantage of this over other methods is that you can easily use queries like the one above as subqueries in your other queries.

Upvotes: 53

Sh4dy
Sh4dy

Reputation: 172

Nowadays, people use DESC instead of DESCRIPTION. For example:- DESC users;

Upvotes: 1

Raja Nagendra Kumar
Raja Nagendra Kumar

Reputation: 790

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='bodb' AND TABLE_NAME='abc';

works for getting all column names

Upvotes: 3

ashish
ashish

Reputation: 221

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='products';

where Table_schema is database name

Upvotes: 22

LGG
LGG

Reputation: 538

A variation of the first answer that I found useful

Open your command prompt and enter (you dont have to be logged into your mysql server)

mysqldump -hlocalhost -u<root> -p<password>  <dbname>  --compact --no-data > </path_to_mydump/>mysql.dmp

Upvotes: 13

Eemeli Kantola
Eemeli Kantola

Reputation: 5557

To get the whole database structure as a set of CREATE TABLE statements, use mysqldump:

mysqldump database_name --compact --no-data

For single tables, add the table name after db name in mysqldump. You get the same results with SQL and SHOW CREATE TABLE:

SHOW CREATE TABLE table;

Or DESCRIBE if you prefer a column listing:

DESCRIBE table;

Upvotes: 133

soulmerge
soulmerge

Reputation: 75764

That's the SHOW CREATE TABLE query. You can query the SCHEMA TABLES, too.

SHOW CREATE TABLE YourTableName;

Upvotes: 17

duckyflip
duckyflip

Reputation: 16509

using this:

SHOW CREATE TABLE `users`;

will give you the DDL for that table

DESCRIBE `users`

will list the columns in that table

Upvotes: 38

Related Questions