Re: How to get data type info for a column?

[Simon Oliver]

The attached script gets just about all the info available for a set of
tables (not including experimental features such as primary and foreign
keys).

--
  Simon Oliver

Cliff wrote:
> 
> 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?
> 
> Cheers :)
> 
> Cliff.


#!/usr/bin/perl
use DBI;
use strict;
use warnings;
use Data::Dumper;

my ($dsn, $dbd, $uid, $pwd, $attr);

$dbd = 'ODBC';
$dsn = 'Orders';
$uid = ''; $pwd = '';
$attr = {PrintError=>0, RaiseError=>1};

my $dbh = DBI->connect("dbi:$dbd:$dsn", $uid, $pwd, $attr) 
  or die "Can't connect to '$dsn' using '$dbd'\n";

my $type_info_all = $dbh->type_info_all();
my $type_info_indexes = $type_info_all->[0];
my $sql_type_index = $type_info_indexes->{SQL_DATA_TYPE};

my %types;
foreach my $type (@$type_info_all[1..@$type_info_all-1]) {
  my %info;
  foreach my $item (keys %$type_info_indexes) {
    $info{$item} = $type->[$type_info_indexes->{$item}];
  }
  $types{$type->[$sql_type_index]} = \%info;
}

my $tables = get_tables($dbh->table_info);

foreach my $table (keys %$tables) {
  my ($sql, $sth);
  $sql = qq{SELECT * from $table WHERE 1=0}; #may need to modify
  eval {
    $sth = $dbh->prepare($sql);
    $sth->execute();
    for my $i (0..@{$sth->{NAME}}-1) {
      my $column = $sth->{NAME}->[$i];
      $tables->{$table}->{$column} = {
        orderinal   => $i,
        name        => $sth->{NAME}->[$i],
        type        => $sth->{TYPE}->[$i],
        precision   => $sth->{PRECISION}->[$i],
        scale       => $sth->{SCALE}->[$i],
        nullable    => $sth->{NULLABLE}->[$i],
        type_name   => $types{$sth->{TYPE}->[$i]}->{TYPE_NAME},
      };
    }
    $sth->finish();
  };
  
  if ($@) {
    print "$table: Error retrieving column info:\n\t", $@, "\n";
  }
}

$dbh->disconnect();

print Dumper($tables);

sub get_tables {
  my $sth = shift or return;
  my $sep = shift;
  $sep = '.' unless defined $sep;
  my %tables;
  while (my $table = $sth->fetch) {
    my @q;
    for my $i (0..2) {
      push @q, quote($table->[$i]) if (defined $table->[$i]);
    }
    my $name = join $sep, @q;
    $tables{$name} = {
      cat     => $table->[0],
      schema  => $table->[1],
      name    => $table->[2],
      type    => $table->[3],
      remarks => $table->[4],
    }
  }
  return \%tables;
}

sub quote {
  my ($val, $ql, $qr)  = @_;
  $ql = '"' unless defined $ql;
  $qr = $ql unless defined $qr;
  return "$ql$val$qr";
}