Meta/structural things like listing all the tables are specific to each database engine, rather than a part of the common SQL standard. The closest sqlite equivalent I'm aware of is the .schema command, which will show you all of the table and index definitions in the database. You can also use .schema tablename to show only a single table definition.

But, yes, in the DBI context, it's probably better to use the generic methods provided by DBI, as already suggested by Corion.