You want to use the DBI "catalog methods" to retrieve table information. That way, your code will also work on other databases than SQLite:

use Data::Dumper;
my $sth = $dbh->table_info;
my $columns = $sth->fetchall_arrayref({});
print Dumper $columns;
[download]

Please do not compare database specific syntax. DBI is written to support the ANSI SQL syntax and not port any weird CLI command syntax, so .schema cannot be used through the DBI unless the driver (DBD::SQLite) has chosen to support that through a ->func call (which it did not).

$ perl -MDBI -wE'my$dbh=DBI->connect("dbi:SQLite:dbname=db.1");say for
+$dbh->tables'
"main"."sqlite_master"
"temp"."sqlite_temp_master"
"main"."foo"

$ perl -MDBI -MData::Peek -wE'DDumper(DBI->connect("dbi:SQLite:dbname=
+db.1")->table_info(undef,"main","foo",undef)->fetchall_arrayref({}));
+'
[
    {   REMARKS          => undef,
        TABLE_CAT        => undef,
        TABLE_NAME       => 'foo',
        TABLE_SCHEM      => 'main',
        TABLE_TYPE       => 'TABLE',
        sqlite_sql       => 'CREATE TABLE foo (c_foo integer not null 
+primary key, foo varchar (20))'
        }
    ]

$ perl -MDBI -MData::Peek -wE'my $sth=DBI->connect("dbi:SQLite:dbname=
+db.1")->prepare("select * from main.foo");$sth->execute;my@f=@{$sth->
+{NAME_lc}};my%ti;for$a(0..$#f){$ti{$f[$a]}{$_}=(@{$sth->{$_}})[$a]for
+ qw(NAME NAME_lc NAME_uc NULLABLE PRECISION SCALE TYPE)};DDumper\%ti;
+'
{   c_foo            => {
        NAME             => 'c_foo',
        NAME_lc          => 'c_foo',
        NAME_uc          => 'C_FOO',
        NULLABLE         => 0,
        PRECISION        => undef,
        SCALE            => undef,
        TYPE             => 'integer'
        },
    foo              => {
        NAME             => 'foo',
        NAME_lc          => 'foo',
        NAME_uc          => 'FOO',
        NULLABLE         => 1,
        PRECISION        => undef,
        SCALE            => undef,
        TYPE             => 'varchar (20)'
        }
    }
[download]

These examples should work on every database (Oracle, Unify, MariaDB, SQLite, CSV, PostgreSQL, MySQL, Sybase, SQL-Server, Informix, Ingres, …), given that you use the correct arguments. e.g. SQLite's default schema is "main", where the default schema for PostgreSQL is "public".

It is up to you to combine all this data into something that works for you. Don't expect PostgreSQL to fix the brainfarts and idotic quotation of mysql. Don't expect Oracle to support the meta-commands from the SQLite CLI. Don't expect Unify to support all data tapes supported in PostgreSQL. Etc etc. There is no ideal database, they all suffer something that makes them unusable in certain circumstances. If you start with "All databases suck", it is easier to choose the one that sucks less than all the other options *for your project*. Sometimes that will be SQLite, sometimes it will be PostgreSQL, sometimes even Oracle. YMMV. (it also depends on the support of the database of your choice on the platform you work on Linux/Solaris/HP-UX/AIX/Windows/OSF-1/Android/iOS/... and even the underlying file-system (XFS, HFS, JFS, FAT, NTFS, Btrfs, Ext4, …)

«…choose the one that sucks less…»

They all suck. I’m pretty sure of this as in the famous last company I was with we supported MySQL, Oracle, Sybase and Microsoft SQL Server. One might say that this is/was yet another case of hubris. And this is true.

«The Crux of the Biscuit is the Apostrophe»

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

Is it possible to execute .schema from the DBI or is it a cli client only thing?

I don't actually know. I've never tried using .schema (or show tables with mysql) in DBI code, only in the interactive clients. When I've wanted to get a database's list of tables from DBI code, I've done it with my @tables = $dbh->tables(undef, undef, undef, 'TABLE'); (Documentation)

To list just the user generated actual tables (not index'es and excluding the statistic tables that SQLite maintains):

Use a select statement like this (tested on one of my DB's using SQLite Manager):

SELECT name FROM sqlite_master WHERE TYPE = 'table' AND 
    name NOT LIKE 'sqlite_%';
[download]

For fun, SELECT * instead of tbl_name and see what all is in this thing. For example includes the SQL code that generated the table, etc.

I like the SQLite Manager browser plugin, but for security reasons, it doesn't run on Chrome or Firefox anymore. So I installed Waterfox 64 bit browser just for the purpose of running this one plugin. I don't use Waterfox for any real browsing.

Update: I don't know if there is a standard SQL way of getting this information or not? That select statement is specific to an SQLite DB. So it won't work on another DB. But this will get you what you want from SQLite.

This was intended to display to a web page using CGI - but you can strip that out.

See also SQLite Describe Table.

This one doesn't show how to do it in perl.

Sure. Nothing but the truth. But the friendly manual I linked to isn’t about Perl. It is about SQLite. With the according driver DBD::SQLite and DBI I guess it should work. Probably with something like this:

use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
my $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
…;
[download]

Uncomplete and not tested and it doesn’t compile, I know. And I suppressed or forgot the ugly details. As you like. The rest is left as an exercise to the reader.

«The Crux of the Biscuit is the Apostrophe»