note
graff
<i>
Go into the command line and type SHOW CREATE tablename...
</i>
<P>
That's one of my favorites, too. In fact, after I wrote a perl command line tool to let me run SQL queries directly via the bash shell, I created this little shell function, which has come in very handy:
<c>
sct ()
{
dbget -a $1 -s "show create table $2" |
perl -pe 's/^\S+\t//; s/, /,\n/g;s/\( /(\n/;s/\) \)/)\n)/'
}
</c>
My perl script "dbget" has its own hash (actually, the hash is in a separate module) of known database connection credentials for whatever servers, databases and user accounts I need to connect to (with a simple mnemonic string as a hash key to identify each one); "dbget" takes a db/user identifier "-a ...", and an sql select or show statement "-s ...", connects to the chosen database, runs the given query, and spits the result to stdout (as tab-delimited lines).
<P>
But the raw output of "show create table ..." contains the whole list of column definitions, etc, in one "field" (on one line of output), which can be hard to read, so the shell function just pipes that to a perl one-liner to put line breaks in the right places.
<P>
This way, whenever I want to see the output of "show create table table_name" (e.g. to see whether a given column is being indexed), I just do this in a bash shell:
<c>
sct db_user_id table_name
</c>
Update: This just occurred to me: in order to do "proper" tab-delimited output, my "dbget" tool has a default behavior that normalizes all field-internal whitespace strings to single spaces. But it has an option to "keep" field-internal whitespace as-is. So the perl one-liner shown above would only apply if the output from "show create table" were filtered to convert field-internal line-feeds to spaces. My shell function could have been stated more simply as:
<c>
sct ()
{
dbget -a $1 -s "show create table $2" -w keep | cut -f2
}
</c>
941152
941355