How to get database structure in MySQL via query

Question

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

User · Answer

SELECT   FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE SCHEMA    test  AND TABLE NAME   products      where Table schema is database name

User · Answer

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 gt      mysql -uroot -p lt password gt  -h lt host gt  -P lt port gt  -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 gt   like following     mysqlshow -uroot -p lt password gt  -h lt host gt  -P lt port gt        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              ------- ------------- ------------------- ------ ----- --------- ---------------- --------------------------------- ---------

User · Answer

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 lt root gt  -p lt password gt    lt dbname gt   --compact --no-data  gt   lt  path to mydump  gt mysql dmp

User · Answer

That s the SHOW CREATE TABLE query  You can query the SCHEMA TABLES  too   SHOW CREATE TABLE YourTableName

User · Answer

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

User · Answer

SELECT COLUMN NAME  FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE SCHEMA  bodb       AND TABLE NAME  abc    works for getting all column names

User · Answer

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

User · Answer

using this   SHOW CREATE TABLE  users     will give you the DDL for that table  DESCRIBE  users    will list the columns in that table

User · Answer

Nowadays  people use DESC instead of DESCRIPTION  For example - DESC users

User · Answer

I think that what you re after is DESCRIBE  DESCRIBE table    You can also use SHOW TABLES  SHOW TABLES    to get a list of the tables in your database

[mysql] How to get database structure in MySQL via query

Examples related to mysql