Get full "path" of SQL tables with R DBI package

Question

I'm new to SQL so I don't know the correct wordings, sorry for that.

When I establish a connection with dbConnect of the DBI package, I can list all tables of the database:

> head(dbListTables(conn))
[1] "cdw_apps"                   "cdw_attachments"           
[3] "cdw_auditLogs"              "cdw_blueprints"            
[5] "cdw_businessObjects"        "cdw_businessObjectsActions"

However I can't access directly to these tables, they are nested (in some kind of "folders", I don't know the wording, again).

For example I have to do:

dbGetQuery(
  conn, 
  "select top 100 * from [vxda-prod-sqldw01].[vxrd_cdw].[cdw_apps]"
)

Is it possible to list the tables with their "path" (I mean [vxda-prod-sqldw01].[vxrd_cdw].[cdw_apps] and not only cdw_apps)?

Answer 1

Most databases support information_schema.tables (and .columns, .routines, and others I believe), and SQL Server is among them. While there are newer object-discovery tables/mechanisms specifically in T-SQL, these still work and are sufficient for this need.

For instance,

DBI::dbGetQuery(conn, "select * from information_schema.tables")
#   TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
# 1     mycatalog          dbo     table1 BASE TABLE
# 2     mycatalog          dbo     table2 BASE TABLE
# 3     mycatalog          dbo   someview       VIEW

will return all tables (and views) visible.