dbTableStructure: List all tables in a database

In MARC-KC/marcR: Helpful Functions Used By The MARC-GIS Team

List all tables in a database

Description

A more informative version of DBI::dbListTables() which only contains table names. This functions also pairs each table with its schema and can handle checking if the table has spatial data and identify if it is a view.

Usage

dbTableStructure(
  conn,
  addGeoIndicator = FALSE,
  includeViews = TRUE,
  rmTableRegex = c("^[:alpha:][:digit:]+$", "^SDE_"),
  rmSchemaRegex = c("sde")
)

Arguments

conn	A DBIConnection object, as returned by dbConnect().
addGeoIndicator	TRUE/FALSE. Should the isSpatial column be exported? Default is FALSE.
includeViews	TRUE/FALSE. Should output included views?
rmTableRegex	Character vector containing table names to avoid searching (removed table regex). Ignores some tables that are only used by the ESRI SDE bindings that don't actually contain user created data.
rmSchemaRegex	Character vector containing schema to avoid searching (removed schema regex). Ignores some default system level schema and schema only used by the ESRI SDE bindings that don't actually contain user created tables.

Value

A dataframe with a row for each table in the database connection. Contains 4 or 5 columns ('Database', 'Schema', 'Table', 'isView', and optionally 'isSpatial'). The return dataframe can then easily be searched, filtered, and queried to find the tables you were looking for.

Author(s)

Jacob Peterson

Examples

## Not run: 

conn <- connectODBC("<servername>.<databaseName>.<schemaName>")

#This is not set up to be a real example
tables <- dbListTableStructure(conn, addGeoIndicator = TRUE)

## End(Not run)

MARC-KC/marcR documentation built on June 2, 2022, 9:31 p.m.