Geek Answers Handbook

How to get list of indexes in SQL table using Perl?

How can I get a list of indexes in a table in my sybase database using Perl? The goal is to “copy” all indexes from the table into an almost identical table.

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

+4
source share
2 answers

There is a statistics_info () method in DBI, but, unfortunately, the only DBD I saw it has implemented so far - DBD :: ODBC. Therefore, if you use ODBC (update: or PostgreSQL!), You are in luck. Otherwise, sp_helpindex (or the sysindexes table) is about as good as it is for Sybase.

Here is what I used for Sybase (in my own OO module - it returns only unique indexes if the all_indexes argument is not 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 just to copy the indices, perhaps you should get the dbschema.pl utility (which uses Sybase :: DBlib), It will generate CREATE INDEX statements for you.

+2
source 
 SELECT i.* FROM sysobjects o, sysindexes i WHERE o.name = $name AND i.id = o.id
0
source

More articles:


All Articles