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

[Andy Todd]

On 15/05/12 12:06 PM, Vernon Cole wrote:
> Eddy:
>    As you discovered, every different database system has a unique way
> of getting a table listing.  The problem with attempting to make a
> uniform tool for doing that at the PEP 249 level, is that some api
> packages (such as the one I maintain for Microsoft ADO, and anything
> that does ODBC) may attach to dozens of different engines.  Mine will
> basically hook up to anything _except_ sqlite. I have no idea which
> engine or engines may show up at the other end.  I attempt to let the
> user determine that by prividing the  non-standard attributes
> connection.dbs_name and connection.dbs_version.  (I borrowed the idea
> from mxodbc.)  The most we should hope for at the PEP 249 level would be
> to have those attributes become part of the standard.
>
>    I think that it would be wonderful if someone (you could volunteer)
> were to make a package which does exactly what you suggest. That package
> would have to determine what the underlying engine is, then give the
> correct commands. If you write it, I will use it.  I'ld even contribute
> the query for Microsft SQL tables.
> --
> Vernon Cole
>
> On Mon, May 14, 2012 at 8:33 AM, Edward Welbourne <eddy at chaos.org.uk
> <mailto:eddy at chaos.org.uk>> wrote:
>
>     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 !

This is hard. Gerald [1] attempts to provide a compact but meaningful 
and consistent data dictionary for a number of backends (currently 
Oracle, MySQL and Postgres). Patches or suggestions for improvement - 
including a module for SQLite - are welcome.

The industry standard (if such a term can be used) is for relational 
databases to provide an INFORMATION_SCHEMA [2]. The problem with this is 
that where it is provided (and AFAIK SQLite doesn't) not all databases 
are equal (MySQL doesn't provide 'referential_constraints' for instance) 
and some key pieces of information aren't included. Although to be fair 
these are usually elements such as tablespace which are implementation 
specific so wouldn't be expected to be available in 'standard' views.

[1] http://halfcooked.com/code/gerald/
[2] https://en.wikipedia.org/wiki/Information_Schema

Regards,
Andy
-- 
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/