MSQL: dbListTables does not return temporary tables

[morgan-dgk]

Issue Description and Expected Result

odbc::dbListTables does not return temporary tables unless catalog_name parameter is provided. DBI specification and documentation suggests that temporary tables should be returned when default parameters are used.

Database

Microsoft SQL Server Version: 13.00.7016

Reproducible Example

library(odbc)
library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "MicrosoftSQL")

DBI::dbExecute(con, 
               "CREATE TABLE ##global_temp (
                  colA varchar(10)
               )")

#output does not include ##global_temp table
odbc::dbListTables(con)

#output includes ##global_temp table
odbc::dbListTables(con, catalog_name = "tempdb")

Session Info

devtools::session_info()
─ Session info ──────────────────────────────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.2.1 (2022-06-23 ucrt)
 os       Windows 10 x64 (build 19044)
 system   x86_64, mingw32
 ui       RStudio
 language (EN)
 collate  English_Australia.utf8
 ctype    English_Australia.utf8
 tz       Australia/Sydney
 date     2022-09-14
 rstudio  2022.02.3+492 Prairie Trillium (desktop)
 pandoc   NA

─ Packages ──────────────────────────────────────────────────────────────────────────────────────────
 package     * version date (UTC) lib source
 bit           4.0.4   2020-08-04 [1] CRAN (R 4.1.3)
 bit64         4.0.5   2020-08-30 [1] CRAN (R 4.1.3)
 blob          1.2.3   2022-04-10 [1] CRAN (R 4.1.3)
 brio          1.1.3   2021-11-30 [1] CRAN (R 4.1.3)
 cachem        1.0.6   2021-08-19 [1] CRAN (R 4.1.3)
 callr         3.7.0   2021-04-20 [1] CRAN (R 4.1.3)
 cli           3.3.0   2022-04-25 [1] CRAN (R 4.1.3)
 clipr         0.8.0   2022-02-22 [1] CRAN (R 4.1.3)
 crayon        1.5.1   2022-03-26 [1] CRAN (R 4.1.3)
 DBI         * 1.1.3   2022-06-18 [1] CRAN (R 4.1.3)
 desc          1.4.1   2022-03-06 [1] CRAN (R 4.1.3)
 devtools    * 2.4.3   2021-11-30 [1] CRAN (R 4.1.3)
 dplyr       * 1.0.9   2022-04-28 [1] CRAN (R 4.1.3)
 ellipsis      0.3.2   2021-04-29 [1] CRAN (R 4.1.3)
 fansi         1.0.3   2022-03-24 [1] CRAN (R 4.1.3)
 fastmap       1.1.0   2021-01-25 [1] CRAN (R 4.1.3)
 fs            1.5.2   2021-12-08 [1] CRAN (R 4.1.3)
 generics      0.1.2   2022-01-31 [1] CRAN (R 4.1.3)
 glue          1.6.2   2022-02-24 [1] CRAN (R 4.1.3)
 hms           1.1.1   2021-09-26 [1] CRAN (R 4.1.3)
 lifecycle     1.0.1   2021-09-24 [1] CRAN (R 4.1.3)
 magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.1.3)
 memoise       2.0.1   2021-11-26 [1] CRAN (R 4.1.3)
 odbc          1.3.3   2021-11-30 [1] CRAN (R 4.1.3)
 pillar        1.7.0   2022-02-01 [1] CRAN (R 4.1.3)
 pkgbuild      1.3.1   2021-12-20 [1] CRAN (R 4.1.3)
 pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.1.3)
 pkgload       1.2.4   2021-11-30 [1] CRAN (R 4.1.3)
 prettyunits   1.1.1   2020-01-24 [1] CRAN (R 4.1.3)
 processx      3.5.3   2022-03-25 [1] CRAN (R 4.1.3)
 ps            1.7.0   2022-04-23 [1] CRAN (R 4.1.3)
 purrr         0.3.4   2020-04-17 [1] CRAN (R 4.1.3)
 R6            2.5.1   2021-08-19 [1] CRAN (R 4.1.3)
 Rcpp          1.0.8.3 2022-03-17 [1] CRAN (R 4.1.3)
 readr       * 2.1.2   2022-01-30 [1] CRAN (R 4.1.3)
 remotes       2.4.2   2021-11-30 [1] CRAN (R 4.1.3)
 rlang         1.0.2   2022-03-04 [1] CRAN (R 4.1.3)
 rprojroot     2.0.3   2022-04-02 [1] CRAN (R 4.1.3)
 rstudioapi    0.13    2020-11-12 [1] CRAN (R 4.1.3)
 sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.1.3)
 testthat      3.1.4   2022-04-26 [1] CRAN (R 4.1.3)
 tibble        3.1.7   2022-05-03 [1] CRAN (R 4.1.3)
 tidyselect    1.1.2   2022-02-21 [1] CRAN (R 4.1.3)
 tzdb          0.3.0   2022-03-28 [1] CRAN (R 4.1.3)
 usethis     * 2.1.6   2022-05-25 [1] CRAN (R 4.1.3)
 utf8          1.2.2   2021-07-24 [1] CRAN (R 4.1.3)
 vctrs         0.4.1   2022-04-13 [1] CRAN (R 4.1.3)
 withr         2.5.0   2022-03-03 [1] CRAN (R 4.1.3)
─────────────────────────────────────────────────────────────────────────────────────────────────────

[simonpcouch]

Can reproduce on SQL Server 2022 (RTM-CU10) - 16.0.4095.4.

[hadley]

It looks like we need a custom dbListTables() method for SQL server; we already provide custom dbExistsTable(). Maybe when the catalog is not supplied, we automatically append the contents of the tempdb catalog?