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?
Add a comment
|
11 Answers
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.
answered May 22, 2009 at 16:18
Bill the Lizard
407k213 gold badges579 silver badges892 bronze badges
1 Comment
Tarik
To target specific database use:
SHOW TABLES FROM database_nameTo 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;
2 Comments
Shai
show create table was exactly what I was looking for. Thanks!
Ari Waisberg
this is not the solution but i'm gratefull because I didn't know this function exists and is great!
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.
3 Comments
Dimitry K
Isn't it that it should be
information_schema.columns (using columns table instead of tables? Because tables doesn't contain any info on which types are the the table columns
bortunac
2nd advantage is that information schema is loaded in memory at server start so no hdd reading neccesary
Russell Fulton
yes, definitely should be COLUMS rather than TABLES! That works a treat!
using this:
SHOW CREATE TABLE `users`;
will give you the DDL for that table
DESCRIBE `users`
will list the columns in that table
4 Comments
duckyflip
soulmerge, sure it's a DDL statement for that table
soulmerge
Yes, it's a statement in a DDL, but a C function is not by itself C. C is a language, a function in a C program is a construct within that language.
Alex Martelli
@soulmerge, I'm a pedant too, but I think you're wrong here: "The French for 'thanks' is 'merci'" is quite an acceptable English sentence (no less than "The French WORD for", etc), and this generalizes to "The {{language name}} for {{thing to express}}" such as "The DDL for this table". "A C function is not by itself C" is the same as saying "A French word is not by itself French": well it's not ALL of French of course, but saying "'Au revoir' is French" is hardly objectionable (as obviously it means it's PART OF French, not ALL OF French!-).
xpt
Note that the OP not only asked for the structure of the whole MySQL database, but also some individual table as well, which is what I was looking for. And this solution answers my question perfectly.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME ='products';
where Table_schema is database name
2 Comments
Shan
is there a way to select information schema. columns - tables from particular db ?
Ahmed Numaan
I used this query on my end
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_db_name' AND TABLE_NAME ='users'; That's the SHOW CREATE TABLE query. You can query the SCHEMA TABLES, too.
SHOW CREATE TABLE YourTableName;
Comments
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
1 Comment
Thufir
I like this, it gives the create statements.
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;
Comments
In the following example,
playgroundis the database name andequipmentis 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 | |
+-------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
Comments
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='bodb'
AND TABLE_NAME='abc';
works for getting all column names
Comments
Nowadays, people use DESC instead of DESCRIPTION. For example:-
DESC users;
