List the table-like objects accessible from an ODBC connection. What objects are ‘table-like’ depends on the DBMS, ODBC driver and perhaps even the configuration settings: in particular some connections report system tables and some do not.
1 2 3
connection handle as returned by
logical: (where supported) should arguments be interpreted literally or including wildcards?
A data frame on success, or character/numeric on error depending on
errors argument. (Use
further details of errors.)
The column names depend on the database, containing a third column
TABLE_NAME (not always in upper case): however, they are
supposed to be always in the same order.
The first column is the ‘catalog’ or (in ODBC2 parlance)
‘qualifier’, the second the ‘schema’ or (ODBC2)
‘owner’, the third the name, the fourth the table type (one of
"SYNONYM", or a driver-specific type name) and the fifth
column any remarks.
Oddly, the Microsoft Excel driver considers worksheets to be system tables, and named ranges to be tables.
Whether the additional arguments are implemented and what they do is
driver-specific. The standard SQL wildcards are underscore to
match a single character and percent to match zero or more
characters (and often backslash will escape these): these are not used
for table types. All of these drivers interpret wildcards in
tableName, and in
Setting one of
"%" and the
"" should give a list of
available catalogs or schemas, whereas
catalog = "", schema = "", tableName = "", tableType = "%"
should list the supported table types.
catalog refers to a database whereas
schema is mostly ignored, and
literal is ignored. To
list all databases use just
catalog = "%". In the 5.1.x
catalog="db_name", tableName="%" to list the
tables in another database, and to list the table types use the
form displayed above.
For PostgreSQL's ODBC driver
catalog is ignored (except that
catalog = "" is required when listing schema or table types) and
literal works for both
schema and for
SQLite ODBC ignores
schema, except that the
displayed form is used to list table types. So although it is
possible to attach databases and to refer to them by the dotted
name notation, it is apparently impossible to list tables on attached
Microsoft SQL Server 2008 interprets both
literal = TRUE it only finds tables if
set (even to an empty string). Schemas are only listed if they contain
Oracle's Windows ODBC driver finds no matches if anything non-empty is
supplied for the
catalog argument. Unless a schema is
specified it lists tables in all schemas. It lists available table
types as just
"VIEW", but other types appear
in listings. With
literal = TRUE it only finds tables if
schema is set (even to an empty string).
DB2 implements schemas but not catalogs.
literal = TRUE has no
effect. In some uses case matters and upper-case names must be used
The Microsoft Access and Excel drivers interpret
catalog as the
name of the Access
.mdb or Excel
.xls file (with the
path but without the extension): wildcards are interpreted in
catalog (for files in the same folder as the attached database)
schema is an error except when
listing catalogs or table types. The Excel driver matched
tableType = "TABLE" (a named range) but not
"SYSTEM TABLE" (the type returned for worksheets).
The Actual Technologies Access/Excel driver ignores all the additional arguments.
Michael Lapsley and Brian Ripley
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
## Not run: > sqlTables(channel, "USArrests") ## MySQL example TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS 1 ripley USArrests TABLE ## PostgreSQL example TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS 1 ripley public usarrests TABLE ## Microsoft Access example > sqlTables(channel) TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS 1 C:\bdr\test <NA> MSysAccessObjects SYSTEM TABLE <NA> 2 C:\bdr\test <NA> MSysACEs SYSTEM TABLE <NA> 3 C:\bdr\test <NA> MSysObjects SYSTEM TABLE <NA> 4 C:\bdr\test <NA> MSysQueries SYSTEM TABLE <NA> 5 C:\bdr\test <NA> MSysRelationships SYSTEM TABLE <NA> 6 C:\bdr\test <NA> hills TABLE <NA> 7 C:\bdr\test <NA> USArrests TABLE <NA> ## End(Not run)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.