Go into the command line and type SHOW CREATE tablename...

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: 

sct ()
{
 dbget -a $1 -s "show create table $2" | 
  perl -pe 's/^\S+\t//; s/, /,\n/g;s/\( /(\n/;s/\) \)/)\n)/'
}

[download]
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).

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.

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: 

 sct  db_user_id  table_name

[download]
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: 
sct ()
{
 dbget -a $1 -s "show create table $2" -w keep | cut -f2
}

[download]

In reply to Re^4: Loosing value and my mind on this one, help!!! by graff
in thread Loosing value and my mind on this one, help!!! by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.