If you want something that's quick-and-dirty, may you should consider doing this:
STEP 01) Use mysqldump
mysqldump --no-data --all-databases > MySQLSchema.sql
STEP 02) Parse the text
Every CREATE TABLE would mark the next databse
Every USE dbname marks the name of the next database
Have each CREATE TABLE description in a text file
STEP 03) Do a LOAD DATA INFILE of each of the parsed files into a table of your choice (such as mydb.mytables)
STEP 04) Query for the structures
SELECT table_desc FROM mydb.mytables WHERE dbname="...' AND tbname='...';
Your your would look something like this:
CREATE TABLE mytables
(
dbname VARCHAR(64) NOT NULL,
tbname VARCHAR(64) NOT NULL,
table_desc TEXT DEFAULT NULL,
primary key (dbname,tbname)
);
Your mission is to parse the mysqldump I just mentioned and get each table description loaded into this table, along with the database anem table name.
Once you construct such a parsing program, you could do this process every hour and update your dynamic documentation.
Give it a Try !!!
UPDATE 2012-04-01 00:39 EDT
Here is modified suggestion: Given the mysqldump suggestion I made, your should load tMySQL Schema attained from the mysqldump into another DB server that contains no data. Set it up as a replication slave using replicate-do-db=mysql. That way, no data will collect in the slave. You can use this slave as the source of the mysqldumping of the DB schema. This separates the data from the schema. You can use your documentation system fetch the schema from the slave.
This is a bug, which I filed and fixed, based on this question. BTW, we do have a bug system too, do not hesitate to use it directly.
Here is the bug report. https://jira.mariadb.org/browse/MDEV-13941
Basically, fragments are created because sparse file is being extended. The fix is not to create sparse files, unless user wants a table with page compression (which is an exotic feature that not many people would use). The fix will appear in the next 10.2 (i.e 10.2.10), mid-end October.
However, just the upgrade to 10.2.10 won't automatically fix the problem for existing tables. There is something else you need to do prior to installation of 10.2.10
- Stop the server
Unset the "sparse flag" on .idb files. in elevated command prompt, type
fsutil sparse setflag C:\path\to\table.ibd 0
- Defragment file with contig (or other tools)
Best Answer
Ever since long, long, ago the info has come from the
.frm
file. The I_S is relatively new. In the future (5.8 maybe) the .frm will be replaced by an InnoDB-based table.CHECK
(and a few other things) are syntactically allowed but are not handled anywhere in the code. (Another example is aDESC
index.) It could be that the .frm contains these strings, but there is no need for such unused information to be replicated in the I_S.