Have synonyms show-up for Microsoft SQL Server dbListTables

[brshallo]

I regularly connect to data marts provisioned for me on Microsoft SQL Server.

When connecting to Microsoft SQL Server, the RStudio "Connections" tab shows "tables" and "views" but does not show "synonyms". ("Synonyms" also do not show-up when running DBI::dbListTables(con))

If I want to view the synonyms in R I will typically do something like:

library(tidyverse)
xtypes <- c("V", "SN", "U")
tables_access <- tbl(con, "sysobjects") %>% 
  select(name, xtype) %>% 
  collect() %>%
  filter(str_detect(xtype, "V|SN|U"))

View(tables_access)

Though this is less convenient. The ability to connect and query synonyms is unaffected. Only the ability to preview tables via DBI::dbListTables / the Connections tab, and only for Microsoft SQL Server.

Right now I end-up usually using Tableau's data previewer in parallel (which I believe is using the same driver but defaults to show synonyms as well as views and tables)...

If it does not make sense to have synonyms show-up by default in the package, do you know what change to options i can make that would make 'synonyms' show-up in my 'Connections' pane in RStudio when connecting to Databases on Microsoft SQL Server?

System details

RStudio Edition : Desktop
RStudio Version :  1.1.282
OS Version      :  Windows 10 Enterprise, x64-based processor
R Version       :  R version 3.4.2 (2017-09-28)

Packages:

dbi pacakge version: 0.8
driver: "SQL Server"

The same thing occurs whether I'm connecting via windows authentication or with a specific username and password with odbc. The lack of synonyms showing-up when running DBI::dbListTables also occurs whether I'm using odbc or RJDBC for connecting.

[brshallo]

Would this be more appropriate under odbc as this is what currently drives the viewer?

[krlmlr]

@jimhester: Is it possible to add support for synonyms to odbc?

[brshallo]

@krlmlr @jimhester note I can still query a synonym, e.g.

tbl(con, "SYN_NAME") %>% collect()

Though the SYN_NAME does not show-up in Rstudio's preview pane nor does it show-up when running dbListTables.

When I run odbc::odbcListObjectTypes(con) I get the following:

$`catalog`
$`catalog`$`contains`
$`catalog`$`contains`$`schema`
$`catalog`$`contains`$`schema`$`contains`
$`catalog`$`contains`$`schema`$`contains`$`table`
$`catalog`$`contains`$`schema`$`contains`$`table`$`contains`
[1] "data"


$`catalog`$`contains`$`schema`$`contains`$view
$`catalog`$`contains`$`schema`$`contains`$view$`contains`
[1] "data"

(Synonyms do not show-up.)

[jimhester]

Yes, it is likely possible to add support for this, but we should move this issue to odbc.

[ghost]

This issue was moved by krlmlr to r-dbi/odbc#221.

[ghost]

This issue was moved by krlmlr to r-dbi/odbc#222.