Retrieve a specific DB project and its tables from SQL Server in R

Viewed 42

I am new to using SQL Server from RStudio. I am connected to SQL Server from RStudio and the server has several different projects listed in the below image. For this work I am using odbc library. I am trying to retrieve the tables of a specific project(Project_3960). I have tried dbListTables(conn,"Project_3960") but this command retrieve the tables from all the projects listed in the below Picture. I just want to retrieve the table which are listed in dbo in Project_3690.

The first picture is from RStudio and the second picture is from SQL Management Studio to show the structure of the folders, in case for executing SQL Query.

Thanks

enter image description here

enter image description here

2 Answers

Click on the arrow to the left of the dbo object under Project_3690, and it should show you the tables you have access to. If it does not, then you have a permissions problem and will need to talk with the DBA. That allows you to see them via the GUI. In fact, if you don't already know the names of the tables you should be accessing (such as to follow my code below), then this is the easiest, as they are already filtering out the system and other tables that obscure what you need.

To see them in R code, then dbListTables(conn) will show you all tables, including the ones in the Connections pane I just described but also a lot of system and otherwise-internal tables that you don't need. On my SQL Server instance, it returns over 600 tables, so ... you may not want to do just that, but you can look for specific tables.

For example, if you know you should have tables Table_A and Table_B, then you could do

alltables <- dbListTables(conn)
grep("table_", alltables, value = TRUE, ignore.case = TRUE)

to see all of the table names with that string in its name.

If you do not see tables that you know you need to access, then it is likely that your connection code did not include the specific database, in which case you need something like:

conn <- dbConnect(odbc(), database="Project_3690", uid="...", pwd="...",
                  server="...", driver = "...")

(Most fields should already be in your connection code, don't use literal ... for your strings.)

One can use a system table to find the other tables:

DBI::dbGetQuery(conn, "select * from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'dbo'")
#   TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
# 1  Project_3690          dbo    Table_A BASE TABLE
# 2  Project_3690          dbo    Table_B BASE TABLE
# 3  Project_3690          dbo    Table_C BASE TABLE

(Notional output but representative of what it should look like.)

Its not quite direct to retrieve the data from SQL server using RStudio when you have different schemas and all are connected to the server. It is easy to view the connected Databases with schema in SQL Server Management Studio but not in RStudio. The easiest way while using Rodbc is to use dot(.) operator and its easy to retrieve the tables of a specific data base is by using "." with dbGetQuery function. I tried dbGetQuery(conn, "select * from project_3690.dbo.AE_ISD ") and it works perfectly fine.

Related