04 August 2008

List Empty Access Tables using Perl

A port of my Python DBI program to Perl. Note that you have to install package DBD-ODBC for the ODBC driver.

use warnings;
use strict;
use DBI;

use constant MDB_PATH => '<path>';

my $dbh = DBI->connect('DBI:ODBC:DRIVER=Microsoft Access Driver (*.mdb);Dbq=' . MDB_PATH);

my $sth = $dbh->prepare(
  "SELECT name FROM MSYSOBJECTS WHERE name NOT LIKE 'MSYS%' AND type = 1");
$sth->execute();
my $ref = $sth->fetchall_arrayref();

for my $row ( @{$ref} )  {
  my $table_name = @$row[0];
  my $sth = $dbh->prepare("SELECT COUNT(*) FROM [$table_name]");
  $sth->execute();
  my @data = $sth->fetchrow_array();
  if ($data[0] == 0) {
    print "$table_name is empty\n";
  }
}

$dbh->disconnect();

See Also

PS

2008-08-08: Replaced sprintf("SELECT COUNT(*) FROM [%s]", $table_name) with "SELECT COUNT(*) FROM [$table_name]" since Perl can evaluate variables in a string.

Added empty parentheses when calling functions to be consistent.