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?
This question is related to
mysql
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='products';
where Table_schema
is database name
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA
.COLUMNS
WHERE TABLE_SCHEMA
='bodb'
AND TABLE_NAME
='abc';
works for getting all column names
In the following example,
playground
is the database name andequipment
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 | |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
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
That's the SHOW CREATE TABLE query. You can query the SCHEMA TABLES, too.
SHOW CREATE TABLE YourTableName;
Nowadays, people use DESC
instead of DESCRIPTION
. For example:-
DESC users;
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.
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;
using this:
SHOW CREATE TABLE `users`;
will give you the DDL for that table
DESCRIBE `users`
will list the columns in that table
Source: Stackoverflow.com