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

[Jeremy Leader]

Most SQL databases also provide a set of meta-data tables describing the 
tables, columns, indexes, etc. in the database.  Unfortunately, I don't 
think the names or layouts of these meta-tables are completely 
standardized, so you'd have to poke around your particular database a bit.

-- 
Jeremy Leader
jleader at alumni.caltech.edu

Matt Campbell wrote:
> 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)*
> 
>     [...]