Have synonyms show-up for Microsoft SQL Server `dbListTables`

[ghost]

@brshallo commented on May 7, 2018, 5:21 PM UTC:

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.

This issue was moved by krlmlr from r-dbi/DBI#244.

[ghost]

@brshallo commented on Aug 6, 2018, 2:25 PM UTC:

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

[ghost]

@krlmlr commented on Aug 26, 2018, 8:32 PM UTC:

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

[ghost]

@brshallo commented on Sep 4, 2018, 5:54 PM UTC:

@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.)

[ghost]

@jimhester commented on Sep 4, 2018, 7:54 PM UTC:

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

[ghost]

@krlmlr commented on Oct 1, 2018, 9:47 AM UTC:

/move to odbc

[brshallo]

Any updates on this? Or any tips on how I might edit a connection object to show synonyms?

[jimhester]

You would have to define a odbcListObjects method for SQL Server that queried the synonyms and included them in the results I guess.