List remote objects

Returns the names of remote objects accessible through this connection as a data frame. This should include temporary objects, but not all database backends (in particular RMariaDB and RMySQL) support this. Compared to dbListTables(), this method also enumerates tables and views in schemas, and returns fully qualified identifiers to access these objects. This allows exploration of all database objects available to the current user, including those that can only be accessed by giving the full namespace.

Methods in other packages

This documentation page describes the generics. Refer to the documentation pages linked below for the documentation for the methods that are implemented in various backend packages.

• pool::dbListObjects("Pool")

• RMariaDB::dbListObjects("MariaDBConnection")

• RPostgres::dbListObjects("PqConnection")

• RSQLite::dbListObjects("SQLiteConnection")

Usage

dbListObjects(conn, prefix = NULL, ...)

Arguments

conn

A DBI::DBIConnection object, as returned by dbConnect().

prefix

A fully qualified path in the database's namespace, or NULL. This argument will be processed with dbUnquoteIdentifier(). If given the method will return all objects accessible through this prefix.

...

Other parameters passed on to methods.

Value

dbListObjects() returns a data frame with columns table and is_prefix (in that order), optionally with other columns with a dot (.) prefix. The table column is of type list. Each object in this list is suitable for use as argument in dbQuoteIdentifier(). The is_prefix column is a logical. This data frame contains one row for each object (schema, table and view) accessible from the prefix (if passed) or from the global namespace (if prefix is omitted). Tables added with dbWriteTable() are part of the data frame. As soon a table is removed from the database, it is also removed from the data frame of database objects.

The same applies to temporary objects if supported by the database.

The returned names are suitable for quoting with dbQuoteIdentifier().

Failure modes

An error is raised when calling this method for a closed or invalid connection.

Specification

The prefix column indicates if the table value refers to a table or a prefix. For a call with the default prefix = NULL, the table values that have is_prefix == FALSE correspond to the tables returned from dbListTables(),

The table object can be quoted with dbQuoteIdentifier(). The result of quoting can be passed to dbUnquoteIdentifier(). (For backends it may be convenient to use the Id class, but this is not required.)

Values in table column that have is_prefix == TRUE can be passed as the prefix argument to another call to dbListObjects(). For the data frame returned from a dbListObject() call with the prefix argument set, all table values where is_prefix is FALSE can be used in a call to dbExistsTable() which returns TRUE.

See also

Other DBIConnection generics: DBIConnection-class, dbAppendTable(), dbAppendTableArrow(), dbCreateTable(), dbCreateTableArrow(), dbDataType(), dbDisconnect(), dbExecute(), dbExistsTable(), dbGetException(), dbGetInfo(), dbGetQuery(), dbGetQueryArrow(), dbIsReadOnly(), dbIsValid(), dbListFields(), dbListResults(), dbListTables(), dbQuoteIdentifier(), dbReadTable(), dbReadTableArrow(), dbRemoveTable(), dbSendQuery(), dbSendQueryArrow(), dbSendStatement(), dbUnquoteIdentifier(), dbWriteTable(), dbWriteTableArrow()

Examples

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbListObjects(con)
#>         table is_prefix
#> 1 <Id> "main"      TRUE
dbWriteTable(con, "mtcars", mtcars)
dbListObjects(con)
#>           table is_prefix
#> 1 <Id> "mtcars"     FALSE
#> 2   <Id> "main"      TRUE
#> 3   <Id> "temp"      TRUE

dbDisconnect(con)