sqlTables: List Tables on an ODBC Connection

Description

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.

Usage

sqlTables(channel, errors = FALSE, as.is = TRUE,
          catalog = NULL, schema = NULL, tableName = NULL,
          tableType = NULL, literal = FALSE)

Arguments

channel

connection handle as returned by odbcConnect.

errors

if TRUE halt and display error, else return -1.

as.is

as in sqlGetResults.

catalog, schema, tableName, tableType

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

  catalog = "", schema = "", tableName = "", tableType = "%"

should list the supported table types.

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.

Examples

Run this code

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