Nik has asked for the wisdom of the Perl Monks concerning the following question:

i use the following to get the table information from my current database i use: 
@tables = $db->tables;

foreach (@tables) {
   print font( {-size=>6, -color=>'Lime'}, "$_<br>" );
}

[download]
whats the appropriate syntax for getting column information for every table listed in my current database? i used the following but it didnt work out! 
@columns = $db->column_info;

foreach (@columns) {
   print font( {-size=>6, -color=>'Lime'}, "$_<br>" );
}

[download]
Can you help me on that? thanks!

Replies are listed 'Best First'.
Re: Column info request.
by stvn (Monsignor) on Feb 04, 2004 at 20:14 UTC
    Nik,

    First, I am not sure what DB module you are using, I am going to assume DBI though. And without the name of the DBD driver you are using it is impossible to actually answer your question.

    In general, getting column info is not that simple, at the very least, you first need to specify the table whose columns you want to examine. To get you started, here is a link to the DBI Database Handle Methods section in the DBI docs, scroll down (for a while) until you see the column_info method, it will tell you were to start at least.

    Bookmark this too (it always comes in handy): The DBI site

    -stvn
[reply]
[d/l]
Re: Column info request.
by jeffa (Bishop) on Feb 04, 2004 at 20:32 UTC
    Here is a VERY MySQL specific way of doing it: 
    use strict;
use warnings;
use DBI;
use CGI::Pretty qw(:standard);

my $dbh   = DBI->connect( ... );
my @table = @{ $dbh->selectcol_arrayref('show tables') };

for my $table (@table) {
   my $sth = $dbh->prepare("select * from $table limit 1");
   $sth->execute();
   print "$table:", br();
   print ol(li $sth->{NAME} );
}

    [download]
    And please do not use the FONT tag to color text. Use CSS instead. :)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
      how about: 
          my $dbfields = $dbh->selectcol_arrayref ("SHOW COLUMNS FROM table"
+);

      [download]
      is even more MySQL'ish and gives all the details of the fields.

      jdtoronto

[reply]
[d/l]
      The oracle equiv query would be "select table_name from user_tables". Just to throw it out there.

      Play that funky music white boy..
[reply]
Re: Column info request.
by blokhead (Monsignor) on Feb 04, 2004 at 20:43 UTC
    I'm not sure if DBI::table_info and DBI::column_info are a pipe dream or not.. From what I've seen around CPAN, any code that needs access to database schemas has employed DBD-specific code/queries for that purpose. I'd would be very interested to see examples of table_info and column_info that work transparently across many DBDs.

    Here are queries you can execute to fetch info about columns and tables, for the drivers I know how: 

    MySQL:
  show tables
  describe TABLE_NAME

SQLite:
  select name from sqlite_master where type='table'
  pragma table_info('TABLE_NAME')

    [download]
    You will also have better luck with table_info and column_info if you look at their usage in the DBI docs -- they return active statement handles, not a list of things. (The tables method does return a simple list, but your usage of it (no arguments) is deprecated -- also note that for many drivers, the tables method returns a lot of system tables, views, etc.. probably more than you want).

    blokhead

[reply]
[d/l]

Back to Seekers of Perl Wisdom