How to get column names from a Perl DBI statement handle?

When working with DBI in Perl, you can retrieve column names from a statement handle using the NAME or NAME_lc attribute after executing a query. These attributes return an array reference containing the column names from your result set.

The $sth->{NAME} attribute returns column names in their original case as provided by the database driver, while $sth->{NAME_lc} returns them in lowercase. There's also NAME_uc for uppercase. These attributes are only populated after a successful execute() call.

Basic Example

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# Create an in-memory SQLite database (no external files needed)
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", "", "", 
    { RaiseError => 1, AutoCommit => 1 });

# Create a sample table
$dbh->do("CREATE TABLE employees (id INTEGER, name TEXT, salary REAL, department TEXT)");
$dbh->do("INSERT INTO employees VALUES (1, 'Alice', 75000.50, 'Engineering')");
$dbh->do("INSERT INTO employees VALUES (2, 'Bob', 65000.00, 'Sales')");

# Prepare and execute a SELECT query
my $sth = $dbh->prepare("SELECT id, name, salary, department FROM employees");
$sth->execute();

# Get column names
my $names = $sth->{NAME};           # Original case
my $names_lc = $sth->{NAME_lc};     # Lowercase
my $num_fields = $sth->{NUM_OF_FIELDS};

print "Number of columns: $num_fields\n";
print "Column names (original): ", join(", ", @$names), "\n";
print "Column names (lowercase): ", join(", ", @$names_lc), "\n\n";

# Practical use: create a hash for each row
print "Data with column names:\n";
while (my $row = $sth->fetchrow_hashref()) {
    print "  ID: $row->{id}, Name: $row->{name}, ";
    print "Salary: \$$row->{salary}, Dept: $row->{department}\n";
}

$sth->finish();
$dbh->disconnect();

Common Pitfalls

• Accessing before execute(): The NAME attributes are only available after calling execute(). Accessing them earlier returns undef.
• Column aliasing: If you use SQL aliases (e.g., SELECT name AS employee_name), the NAME attribute will reflect the alias, not the original column name.
• Array reference: Remember that NAME returns an array reference, not an array. Dereference with @$names or @{$sth->{NAME}}.

This technique works with all DBI-compatible database drivers (MySQL, PostgreSQL, Oracle, SQLite, etc.) and is available in all modern Perl versions with DBI installed (DBI is a core-recommended module).