Mysql – “show create table” output with out “show”

information-schemaMySQL

What

I'd like to be able to get the SQL statement by 'show create table' as something I could do something with along the lines of

insert into mytable (myTextColumn) (show create table blah)

I've been able to find some stored procedures that do this for other brands of SQL than I use. I realize all the required information is in information_schema and I could use that to build the create statement myself. It just seems like this is a problem that's been solved in other flavors and there's likely already a stored proc out there to do what I want w/o reinventing it myself for mysql.

Why

I'm trying to set some dynamic schema introspection as part of documentation. The idea is I'd like to have the doc pages illustrate a current representation of the schema in the various environment it may exist in (dev, qa, prod).

The "obvious" thing to do is just query the dbs and "show create table blah". However doing that requires select access on the table. I don't really care to create an account with global read access to every table in every DB.

I'm looking to either have a restricted script on a cron that runs/collects the output of show create table; or have a mysql event that constructs the output I want in an event. Either way this output would ultimately be persisted to a documentation db w/ just the schemas stored so the documentation app can pull this information without its db account having select access to the actual tables.

I'm tempted to lean toward an internal event that populates this just so there's not an account out there with wide read access.

Why the why

I'm explaining my ultimate goal in case someone can point out a different implementation path or solution that matches my requirements (or convince me my requirements are off to begin with).

Best Answer

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.

Related Question