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, …)

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.