R RODBC Show all tables

Question

I'm trying to catalog the structure of a MSSQL 2008 R2 database using R/RODBC. I have set up a DSN, connected via R and used the sqlTables() command but this is only getting the 'system databases' info.

library(RODBC)

conn1 <- odbcConnect('my_dsn')
sqlTables(conn1)

However if I do this:

library(RODBC)

conn1 <- odbcConnect('my_dsn')
sqlQuery('USE my_db_1')
sqlTables(conn1)

I get the tables associated with the my_db_1 database. Is there a way to see all of the databases and tables without manually typing in a separate USE statement for each?

Solution

There may or may not be a more idiomatic way to do this directly in SQL, but we can piece together a data set of all tables from all databases (a bit more programatically than repeated USE xyz; statements) by getting a list of databases from master..sysdatabases and passing these as the catalog argument to sqlTables - e.g.

library(RODBC)
library(DBI)
##
tcon <- RODBC::odbcConnect(
  dsn = "my_dsn",
  uid = "my_uid",
  pwd = "my_pwd"
)
##
db_list <- RODBC::sqlQuery(
  channel = tcon,
  query = "SELECT name FROM master..sysdatabases")
##
R> RODBC::sqlTables(
    channel = tcon, 
    catalog = db_list[14, 1]
  )

---

(I can't show any of the output for confidentiality reasons, but it produces the correct results.) Of course, in your case you probably want to do something like

all_metadata <- lapply(db_list$name, function(DB) {
  RODBC::sqlTables(
    channel = tcon,
    catalog = DB
  )
})
# or some more efficient variant of data.table::rbindlist...
meta_df <- do.call("rbind", all_metadata)