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

[Matt Campbell]

This was an example of what to do, not a prepackaged solution, there is a
charge for the latter.   ;)

 

You can use the 'show tables;' command once you have connected to a database
to list all the tables.

Once you have a list of the tables and each table's columns you can either
inspect them visually, or use Perl to search for any pattern of the column
names you wish to.  You can use the provided code to report on all matching
column names and extend it to report their data types.  Good luck, write if
you find work..

 

Matt

 

P.S.  This is all included in the Perl DBI documentation and is derived
fairly simply therefrom.

 

# Return a pointer to an array of pointers to arrays containing column names
and data types for the given table

Sub listColumns {

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

 

                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],
$result->[5] ];

                }

                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 1:52 PM
To: SGVLUG Discussion List.
Subject: RE: [SGVLUG] Meta-querying (perhaps OT...)

 

If I'm reading this correctly (though mostly I'm inferring this from the
function name), it appears this lists all of the columns [fields] of the
dataset [table] "people" -- that isn't what I want, I'm after all the tables
that contain the column "first_name".  In addition, (and this is the harder
part), I'd like to know where the DBA chose to name the field "FirstName",
"fName", or simply "name"... [and, to a lesser extent, are these all of
compatible forms, i.e., "varchar(n)" where "n" is consistent among
instances...]

 

-----Original Message-----Of Matt Campbell

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.

 

 

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.

 

 [...] listTableColums($db, 'people');

 

sub listTableColumns {

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

 

 [...]

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

 

 

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)

[...]

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