> I am a new to the world of databases and I am having problems trying to
> return the table structure in an mSQL database using the DBI module. If
> anyone could help please. I dont seem to be able even to come up with a
> proper syntax not recognize the right handle or atribute.
You could read the docs for the DBMS you're using - it will tell you
which tables, views or proceedures provide that information.
If you want a more portable solution look at the DBI tables, table_info,
type_info, etc. Below is a simple script that gets a list of tables and
looks up the column names for each table found.
--
Simon Oliver
#!/usr/bin/perl -w
use DBI;
use strict;
my ($dsn, $dbd, $uid, $pwd, $attr);
$dbd = 'ODBC';
$dsn = 'my_dsn';
$uid = ''; $pwd = '';
$attr = {PrintError=>0, RaiseError=>1};
# make a connection
my $dbh = DBI->connect("dbi:$dbd:$dsn", $uid, $pwd, $attr)
or die "Can't connect to '$dsn' using '$dbd'\n";
# get list of tables
my @tables = $dbh->tables();
foreach my $table (@tables) {
my ($sql, $sth);
# you may need to quote the table name?
$sql = qq{SELECT * from $table WHERE 1=0};
eval {
$sth = $dbh->prepare($sql);
$sth->execute();
};
if ($@) {
print "$table: Error retrieving column names:\n\t", $@, "\n";
} else {
print "$table:\n\t", join("\n\t", @{$sth->{NAME}}), "\n";
$sth->finish();
}
}
$dbh->disconnect();
