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.
I don't see how your two solutions are different... Since it still has to actually CONNECT to a database instance to see what databases are there, I'm willing to bet the only different is HOW you are connecting.
psql -l -A -t
vs
psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"
Try instead
psql -q -A -t -c "SELECT datname FROM pg_database"
This should use the default connection settings (database and login as local account, local connection, 5432 port). If you really need to, then specify the user and database with -U postgres -d postgres .. but otherwise just leave the host and port unspecified.
I'm willing to bet the only reason psql -l -A -t isn't asking for a password is because you have that information in your .pgpass file.
Best Answer
In PostgreSQL (as described in documentation, The Information Schema):
For MySQL you would need
table_schema='dbName'and for MSSQL remove that condition.Notice that "only those tables and views are shown that the current user has access to". Also, if you have access to many databases and want to limit the result to a certain database, you can achieve that by adding condition
AND table_catalog='yourDatabase'(in PostgreSQL).If you'd also like to get rid of the header showing row names and footer showing row count, you could either start the psql with command line option
-t(short for--tuples-only) or you can toggle the setting in psql's command line by\t(short for\pset tuples_only). This could be useful for example when piping output to another command with\g [ |command ].