sqlTables {RODBC} | R Documentation |
List the tables on an ODBC database.
sqlTables(channel, errors = FALSE, as.is = TRUE, ...)
channel |
connection handle as returned by
odbcConnect . |
errors |
if TRUE halt and display error, else return -1 . |
as.is |
as in sqlGetResults . |
... |
additional arguments restricting the scope to be passed
to odbcTables . |
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 column
TABLE_NAME
(not always in upper case: however, they are
supposed to be always in the same order and the table names should be
in the third column.
The first column is the ‘catalog’ or (for 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 data source-specific type name) and the fifth
column any remarks.
Strangely, the 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 ODBC wildcards are underline 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.
For the MySQL drivers, catalog
refers to a database whereas
schema
is ignored. Using just catalog = "%"
will list the databases (including system ones): to list tables in a
database use catalog = "dbname", tableName = "%"
.
For PostgreSQL's ODBC driver catalog
is ignored.
SQLiteODBC ignores catalog
and schema
.
Microsoft SQL Server 2008 interprets both catalog
and schema
.
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.
The 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. The Excel driver
matched tableType = "TABLE"
(a named range) but not
tableType = "SYSTEM TABLE"
(the type returned for worksheets).
Michael Lapsley and Brian Ripley
## Not run: channel <- odbcConnect("test") sqlTables(channel, "USArrests") ## MySQL example TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS 1 ripley public testlogical TABLE ## Microsoft Access example ## 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> close(channel) ## End(Not run)