R RODBC Show all tables

Question

I am trying to catalog the database structure of MSSQL 2008 R2 using R / RODBC. I installed a DSN connected via R and used the command sqlTables(), but this is only getting the "system database" information.

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 tables related to my_db_1 database. Is there a way to see all the databases and tables without manually entering a separate USE statement for each?

Answer 1

SQL, ( , USE xyz;), master..sysdatabases catalog sqlTables -

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

---

( - , .) , , , -

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)