How can I get a list of indices on a SQL table using Perl?

Question

How can I get a list of the indices on a table in my sybase database using Perl? The goal is to "copy" all the indices from a table to a nearly identical table.

Is $dbh->selectarray_ref('sp_helpindex $table') the best I can do?

Answer 1

SELECT i.*
FROM sysobjects o, sysindexes i
WHERE o.name = $name
  AND i.id = o.id

Answer 2

There's a statistics_info() method in DBI, but unfortunately, the only DBD I've seen it implemented in so far is DBD::ODBC . So if you use ODBC (update: or PostgreSQL!) you're in luck. Otherwise sp_helpindex (or the sysindexes table) is about as good as it gets for Sybase.

Here's what I've used for Sybase (in my own OO module - it returns only unique indexes unless the all_indexes argument is true):

{
my $sql_t = <<EOT;
select
  sysindexes.name,
  index_col(object_name(sysindexes.id), sysindexes.indid, syscolumns.colid) col_name
from sysindexes, syscolumns
where sysindexes.id = syscolumns.id
  and syscolumns.colid <= sysindexes.keycnt
  and sysindexes.id = object_id(%s)
EOT

sub index_info {
  my ( $self, $table, $all_indexes ) = @_;

  my $dbh = $self->{DBH};
  my $sql = sprintf $sql_t, $dbh->quote($table);
  $sql .= "and sysindexes.status & 2 = 2\n" unless $all_indexes;
  my $sth = $dbh->prepare($sql);
  $sth->execute();
  my @col_names = @{$sth->{NAME_lc}};
  my %row; $sth->bind_columns(\@row{@col_names});
  my %ind;
  while ($sth->fetch()) {
    if ( $row{col_name} ) {
      push @{$ind{$row{name}}}, lc($row{col_name});
    }
  }
  return unless %ind;
  return \%ind;
}

}

Or if your goal is to just copy indexes, maybe you should get the dbschema.pl utility (which uses Sybase::DBlib). It will generate the "CREATE INDEX" statements for you.