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.
NULL or character:
whether these do anything depends on the ODBC driver. The first
three can be length-one character vectors, and tableType can
specify zero or more types in separate elements of a character vector.
literal
logical: (where supported) should arguments be
interpreted literally or including wildcards?
Value
A data frame on success, or character/numeric on error depending on
the errors argument. (Use sqlGetResults for
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
"TABLE", "VIEW", "SYSTEM TABLE", "ALIAS",
"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.
Driver-specific details
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 catalog or schema where
supported.
Setting one of catalog or schema to "%" and the
other and tableName to "" should give a list of
available catalogs or schemas, whereas
For MySQL, 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
driver, use 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 tableName.
SQLite ODBC ignores catalog and 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
databases.
Microsoft SQL Server 2008 interprets both catalog and schema.
With literal = TRUE it only finds tables if schema is
set (even to an empty string). Schemas are only listed if they contain
objects.
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 "TABLE" and "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
for schemas.
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)
and tableName. Using schema is an error except when
listing catalogs or table types. The Excel driver matched
tableType = "TABLE" (a named range) but not tableType =
"SYSTEM TABLE" (the type returned for worksheets).
The Actual Technologies Access/Excel driver ignores all the additional
arguments.
Author(s)
Michael Lapsley and Brian Ripley
See Also
sqlGetResults
Examples
## 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:dr\test <NA> MSysAccessObjects SYSTEM TABLE <NA>
2 C:dr\test <NA> MSysACEs SYSTEM TABLE <NA>
3 C:dr\test <NA> MSysObjects SYSTEM TABLE <NA>
4 C:dr\test <NA> MSysQueries SYSTEM TABLE <NA>
5 C:dr\test <NA> MSysRelationships SYSTEM TABLE <NA>
6 C:dr\test <NA> hills TABLE <NA>
7 C:dr\test <NA> USArrests TABLE <NA>
## End(Not run)