Not only is it not a Perl question, or even a SQL question, it's a platform-specific MySQL question. In other words, the answer is different for Oracle, DB2, SQL Server, etc. All of these have different catalogs, so the "improved" query would be different. Even knowing the catalog structure won't always help because most databases don't allow variables in the FROM clause: you end up running the same query for each table anyway.

There is a better way to ask if a table has rows, but I'm not sure if MySQL supports it: the EXISTS clause. In Sybase, for example, you could say:

if exists (select 1 from my_table)
    print 'my_table has at least one row'
[download]

Off the top of my head, however, i would try to utilize map or grep. Sometimes they are more elegant when it comes to populating a list with data. Also, the select* functions from DBI allow you to combine prepare, execute, and fetch functions into one call. Something like:

my @empty = grep {
   $dbh->selectcol_arrayref("select count(*) from $_")->[0] == 0
} $dbh->tables;
[download]

UPDATE:
changed to $dbh->tables per gmax's suggestion, but now it looks just like Zaxo's!! :D (i orginally used @{ $dbh->selectcol_arrayref("show tables") })

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

SHOW TABLE STATUS

It also supports LIKE 'MYTABLE%' wildcards.

Regards

Jeff

If your DBI and the MySQL bits are recent, you can do this:

# $dbi handle is available
my @non_empties = grep {
    # almost like jeffa's now
    $dbh->selectcol_arrayref("select count(*) from $_")->[0]
} $dbh->tables;
[download]

Update: rnahi is correct, $sth->rows won't do. Repaired

After Compline,
Zaxo

This won't work as expected.

$sth->rows returns the number of affected rows, and since in this case there are none, (COUNT is not a row affecting command) it will return -1, thus making TRUE all the items in the array. Therefore grep will return all the tables, even the empty ones.

       "selectrow_arrayref"
             $ary_ref = $dbh->selectrow_arrayref($statement);
             $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
             $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @
+bind_values);
           This utility method combines "prepare", "execute" and
           "fetchrow_arrayref" into a single call. It returns the firs
+t row of
           data from the statement.  The $statement parameter can be a
+ previ-
           ously prepared statement handle, in which case the "prepare
+" is
           skipped.
                                                                      
+          
           If any method fails, and "RaiseError" is not set, "selectro
+w_array"
           will return undef.
[download]

just as a note under mySQL I would use limit rather than count. eg.

my $sth = $dbh->prepare("select * from $table limit 1");
$sth->execute();
print "Theres at least 1 row" if $sth->rows();
[download]

That way you don't have to look through the whole table to create the result ( although it's possible that is optimized)

Update: mahi's note below shows just why one should be careful about premature optimization. heh.

Your method will work, but not the way you say.

In MySQL, COUNT(*) (without a WHERE clause) is optimized so that it will get the result from the description table, without physically counting the records.

Thus, your query will be slower than using COUNT. ;)

jeffa's method is the fastest you can get in this case.

Indeed there isn't any way to dynamically set table names in DBI, or indeed in most databases. The reason why comes down to what a ->prepare() is actually doing. (Note: this is somewhat platform specific, so YMMV here). The prepare tells the database to get the SQL ready and parsed so that it can be executed with a variety of parameters in the where clause. This is an efficiency issue for the DB, and there is no way to dynamically avoid it.

So, what can you do?

• To get the row-count you can either do the count(*) (as you mentioned) or retrieve all rows and count them
• To get by the dynamic table problem you can write a simple sub (see below) that takes the table-name and returns you the SQL.
• That sub can be linked with the cute DBI method $dbh->selectrow_array() which can remove the need for the prepare/execute combination and save you a few lines of code

sub make_sql($) { return "SELECT COUNT(*) FROM $_[0]"; }

if ($dbh->selectrow_array(make_sql($tablename))) {
      # do stuff
      }
[download]

A final note: something seems buggy to me in using the selectrow_array method directly in the if condition, but I can't put my finger on what it is. You might want to break it into a separate variable $count = $dbh->selectrow_array(make_sql($tablename));

Update: I just realized that my response is nearly identical to hmerril's just above....