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?
+2
A:
using this:
SHOW CREATE TABLE `users`;
will give you the DDL for that table
DESCRIBE `users`
will list the columns in that table
duckyflip
2009-05-22 16:18:37
-1 DDL is a language, while what you get with SHOW CREATE TABLE is a statement
soulmerge
2009-05-22 16:33:28
soulmerge, sure it's a DDL statement for that table
duckyflip
2009-05-22 16:39:52
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.
soulmerge
2009-05-22 16:45:23
@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!-).
Alex Martelli
2009-05-23 22:44:58
+4
A:
I think
DESCRIBE table;
is what you're after. You can also use
SHOW TABLES;
to get a list of the tables in your database.
Bill the Lizard
2009-05-22 16:18:43
A:
That's the SHOW CREATE TABLE query. You can query the SCHEMA TABLES, too.
SHOW CREATE TABLE YourTableName;
soulmerge
2009-05-22 16:20:09
+3
A:
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.
Zenshai
2009-05-22 16:20:20
A:
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;
Eemeli Kantola
2009-05-22 17:57:18