Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Re^4: Loosing value and my mind on this one, help!!!

by graff (Chancellor)
on Dec 02, 2011 at 22:15 UTC ( #941440=note: print w/replies, xml ) Need Help??

in reply to Re^3: Loosing value and my mind on this one, help!!!
in thread Loosing value and my mind on this one, help!!!

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)/' }
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
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 }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://941440]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (3)
As of 2018-05-21 01:40 GMT
Find Nodes?
    Voting Booth?