Cliff
> Hey there,
>
>
> I am looking for a way to get the data_type info for each column for
> several tables. Ie I wish to loop over several tables and build a
> profile of the column type per table.
>
>
> Anyone got any advice please?
Guess that your specific problem is that you can't find any part in
the docs where i) columns are directly accessible by table names, and
ii) the column properties are directly accessible by the column name.
Try this:
... get $dbh here...
my ($sql, $sth);
my @properties = qw(TYPE PRECISION SCALE NULLABLE);
foreach my $table ($dbh->tables) { # get a list of all tables
print "Current table: $table\n";
$sql = "SELECT * FROM $table WHERE 1=2"; # get a list of all
columns without occupying your database too much
$sth = $dbh->prepare($sql);
$sth->execute;
my $column_index = 0;
foreach my $column (@{$sth->{'NAME'}}) {
print " $column:\n";
foreach my $property (@properties) {
print " $property: " , $sth->{$property}-
>[$column_index] , "\n";
}
$column_index++;
}
}
This should roughly describe what to do. Instead of printing, you can
also put this in a nested data structure. The basic trick is that the
statement handle attributes of interest (NAME, TYPE, PRECISION,
NULLABLE SCALE and hopefully forthcoming ones) return the column
properties as array(refs), so you can't access them by their column
name, but by the respective column index. So if you want column
properties accessible by the column name, you have to get the column
index first by iterating over the NAME list, and then get the other
properties by the current index. Note that you can refine this
approach by using the table_info() method instead of the tables()
method, so you can exclude system tables and views from your
profiling. And, as has been said already by others, it's all in the
exellent docs
HTH
Bodo
Dr. med. Bodo Eing
Institut fuer Medizinische Mikrobiologie
Klinische Virologie
v.-Stauffenbergstr. 36
48151 Muenster
Germany
Phone: ++49 251 7793 111 Fax: ++49 251 7793-104
