[DB-SIG] Getting a list of tables from a database

[Edward Welbourne]

Hi DB-sig,

I'm digging data out of my mobile 'phone so that I don't lose it during
an upgrade.  It turns out to hold my address-book in a sqlite database,
so I duly connected to that using the pysqlite2 package; which
implements PEP 249 (DB API 2.0) - indeed, the nearest it gets to
documentation was something google found me that mentioned it
implemented the PEP.  (Hint to the maintainer if reading this: the
.__doc__ of either pysqlite2 or its .dbapi2 would be a good place to say
that, ideally complete with the PEP's URL.)

I looked at SQL references and at the PEP but nothing tells me a
standardised way to ask a database its list of tables.  SQL tutorials
seem to assume I created the tables, so obviously know their names; the
explorer trying to make sense of a found database isn't considered.  It
would seem that each database has its own idiosyncratic way of getting
such a list, typically be selecting from a special table, whose name
varies from database to database, as does the column name for the names
of tables.

Oracle:
SELECT table_name FROM user_tables
(or SELECT owner, table_name FROM all_tables, or FROM dba_tables).

SQLite:
SELECT name FROM sqlite_master WHERE type='table';
Notice the need for a WHERE clause, in this case.

MS's SQL server uses meta-table sys.tables, if my googling is accurate,
but the source didn't say what column-name to ask for.

Most of SQL is sufficiently standardised to permit writing portable code
to explore a database - provided access compatible with PEP 249 is
available - without need to know details of the SQL implementation; so
it would be nice if some future version of the DB API were to specify a
standard method, of connection objects, that can be used to obtain the
list of tables available via the connection.

I can imagine that most of the other information in meta-tables and
kindred magic is only actually useful in conjunction with other
implementation-specific features of the database, so not worth exposing
in a portable form; but having a list of the table-names I can use in
plain SQL queries is useful in general, without need for reference to
(other) implementation details.

A sufficient solution - for the three sample databases for which I found
details - would be to provide a string global of the module,
implementing the DB API, whose value is an SQL string that can be passed
as the command to its .connect(whatever).cursor().execute(command); but
I suspect it would make better sense to provide a separate method of
connection objects, that simply returns a list of (or iterator over)
table names.

	Eddy.
-- 
Note: I'm not on DB-sig, so please keep me overtly CC'd in replies, if
you care whether I see them !