How to query for MySQL database schematic

Question

In Sqlite, I can query for tables names and their structure like this:

SELECT name, sql 
FROM sqlite_master 
WHERE type = 'table' AND Name NOT LIKE '%sqlite%'

I want to query for database schematic for MySQL database. Some digging in phpMyAdmin, I end with this:

SELECT SCHEMA_NAME, TABLE_NAME 
FROM SCHEMATA, TABLES 
WHERE SCHEMA_NAME = TABLE_SCHEMA AND SCHEMA_NAME ='myDb'

Unfortunately, this will only output tables names. Is there any sql query do the some work of:

mysqldump -u root myDb --no-data=true --add-drop-table=false > myDb.sql

Off-topic rhetorical question (OTRQ): Isn't it difficult to read queries which are written all on one line? — mechanical_meat, Feb 15 '13 at 20:26
not sure how you can join to make this work for all tables, but `DESCRIBE tblname` gives you that information for an individual table — Matt Busche, Feb 15 '13 at 20:33
@MattBusche I need it as sql query because I am going to embed it in my application. — SIFE, Feb 15 '13 at 20:49
Sounds like you can do this in two steps: 1. Get the table-names with a metadata query; 2. loop over those table-names issuing a DESCRIBE statement for each table. — mechanical_meat, Feb 15 '13 at 20:52
@bernie My application is going to be written in C language. — SIFE, Feb 15 '13 at 20:55
@MattBusche It does, except I can't execute `DESCRIBE` as query. — SIFE, Feb 15 '13 at 21:18

score 2 · Answer 1 · edited May 23 '17 at 10:32

2

select * 
from information_schema.columns
where table_schema = 'sqlite_master'
order by table_name, ordinal_position

based on the accepted answer from this SO question

edited May 23 '17 at 10:32

Community

answered Feb 15 '13 at 21:17

Matt Busche

Note that the `INFORMATION_SCHEMA` tables/views are a part of the ISO SQL92 standard. They should be present on all modern RDBMSs. Unfortunately, SQLite currently does not implement these standard metadata tables/views. – Bacon Bits Feb 15 '13 at 21:45

score 1 · Answer 2 · answered Feb 15 '13 at 21:17

1

How about

SHOW CREATE TABLE tablename;

answered Feb 15 '13 at 21:17

Richard O

2 Answers2