1

I'm new to connecting to databases hosted in SQL Server.

The following code works:

library(DBI)
con <- dbConnect(odbc::odbc(), .connection_string = "Driver={SQL Server};", 
                 user = rstudioapi::askForPassword("Your computer usename e.g. SmithJo"), 
                 password = rstudioapi::askForPassword("Your computer password (e.g. to login to Windows)"), 
                 port = 1433, 
                server = "myserver", 
                dbname = "ABC")

However, the connections pane then shows a number of databases: enter image description here

When I use the command dbListTables(con), are the tables listed JUST from database ABC?

Improve this question
11
  • SQL Server returns a LOT of crap^Htables and views and meta-things and such when you list the tables. I wish I knew how to programmatically cut out the system objects. An approximation might be to use this code (or related), as I find its output is tables (and columns) only. Commented Sep 6, 2018 at 16:22
  • I'm pretty new to this so if you had any idea on how to translate that to R...? Commented Sep 6, 2018 at 16:24
  • @r2evans ... if you think SQL Server is bad, try Oracle! I use options(connectionObserver = NULL) at top of script to avoid those other items. Commented Sep 6, 2018 at 16:25
  • No thanks, I'm having enough trouble dealing with MS's implementation of ODBC ... their own protocol, nonetheless, which requires the user to know the underlying storage of some columns. The fact that nanodbc and odbc punt on this flaw makes my deailng with sqlserver in one thread and postgres in another to be ... a nightmare. (RIP, RSQLServer, you are missed.) Commented Sep 6, 2018 at 16:44
  • 1
    @Nova ... err, DBI::dbGetQuery(con, "select * from INFORMATION_SCHEMA.COLUMNS")? Or if you need just table names (as you would get with dbListTables), then DBI::dbGetQuery(con, "select table_name from INFORMATION_SCHEMA.COLUMNS") Commented Sep 6, 2018 at 16:46
Related questions
0
how to connect SQL and R.Net?
7
How to manage a database connection in an R Package
7
Connect to MSSQL using DBI
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.