[SGVLUG] Meta-querying (perhaps OT...)

[Matt Campbell]

Hi Tom,

 

You can do this easily in mySQL if you have privs to open the database.  I
don't know how to do it in DB2 though.

 

Matt

 

P.S.  The following is off the top of my head from some time ago so it
probably needs double checking.

The basic function should work with any DB Perl has drivers for.

 

#!/usr/bin/perl -w

use DBI;

 

our $error = undef;

my $db = {

                'driver' => 'mysql',

                'database' => 'test',

                'host' => 'scar',

                'port' => '3306',

                'user' => 'matt',

                'password' => 'SGV1uG',

};

listTableColums($db, 'people');

exit;

 

sub listTableColumns {

                my ($db, $table) = @_;

 

                my $dsn = "dbi:$db->{'driver'}:database=$db->{'database'};"
.

                                "host=$db->{'host'};port=$db->{'port'}";

                my $dbh = DBI->connect($dsn, $db->{'user'},
$db->{'password'},

                                { RaiseError => 0, AutoCommit => 1 });

                unless ($dbh) {

                                $error = $DBI::errstr;

                                return undef;

                }

                my $sth = $dbh->column_info(undef, undef, $table, undef);

                unless (defined($sth)) {

                                $error = "Error preparing column info
request: $DBI::errstr";

                                return undef;

                }

                $sth->execute();

                my $results = $sth->fetchall_arrayref;

                my $ret = [];

                foreach my $result (@$results) {

                                $ret->[$result->[16] - 1] = $result->[3];

                }

                return $ret;

}

 

From: sgvlug-bounces at sgvlug.net [mailto:sgvlug-bounces at sgvlug.net] On Behalf
Of Emerson, Tom (*IC)
Sent: Monday, July 07, 2008 12:04 PM
To: SGVLUG Discussion List.
Subject: [SGVLUG] Meta-querying (perhaps OT...)

 

This is more for the database guru's out there -- specifically, for DB2: is
there a way to perform a "meta" query on a database?  In particular, I'd
like to find out what tables contain a particular field (by name, or
possibly by structure as it's possible that the "name" of the field isn't
consistent across datasets)

This is (perhaps...) a feature I happen to like with TurobIMAGE databases.
It may seem a little daunting at first, but it "encourages" designing a
database that is easy to maintain.  With IMAGE, you need to specify the
fields that will be used in the database prior to actually placing them in
datasets (tables).  Since the structure of the field is kept independent of
the table specification, you can't inadvertantly define a "key" item as
"varchar(12)" in one table and "numeric(9,3)" in another (you have to
explicitly do this by defining two different fields and relating them as a
key item, but the schema processor will stop and show you the error of your
ways...)

I *think* you can do this with MySQL, but only if you've set up field-level
security in your database (kind of a back-door approach -- you query the
access-rights table for all tables containing the field you are interested
in...)

So, is what I'm after possible, or do I have to dump the schema to a file
and use something like grep? 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.sgvlug.net/pipermail/sgvlug/attachments/20080707/de7136d2/attachment-0001.html