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.
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).