sqlTables {RODBC}R Documentation

List Tables on an ODBC Database

Description

List the tables on an ODBC database.

Usage

sqlTables(channel, errors = FALSE, as.is = TRUE, ...)

Arguments

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.

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 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.

Driver-specific details

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).

Author(s)

Michael Lapsley and Brian Ripley

See Also

sqlGetResults, odbcTables

Examples

## 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)

[Package RODBC version 1.2-6 Index]