Programming the Perl DBI

Database Metadata

Database metadata is high-level information, or “data about data,” stored within a database describing that database. This information is extremely useful for dynamically building SQL statements or even generating dynamic views of the database contents.

The metadata stored by a database, and the way in which it’s stored, varies widely between different database systems. Most major systems provide a system catalog , consisting of a set of tables and views that can be queried to get information about all the entities in the database, including tables and views. There are two common problems with trying to query the system catalog directly: they can be complex and difficult to query, and the queries are not portable to other types of database.

The DBI should provide a range of handy methods to access this information in a portable way, and one day it will. However, currently it only provides two methods that can be executed against a valid database handle to extract entity metadata from the database.

The first of these methods is called tables() , and simply returns an array containing the names of tables and views within the database defined by the relevant database handle. The following code illustrates the use of this method:

### Connect to the database
my $dbh = DBI->connect( 'dbi:Oracle:archaeo', 'stones', 'stones' );

### Get a list of tables and views
my @tables = $dbh->tables();

### Print 'em out
foreach my $table ( @tables ) {
    print "Table: $table\n";
}

Connecting ...