Is it possible to display all database tables in R Notebook after establishing an ODBC connection to a SQL server?

I have established a connection to a SQL server in R Notebook. I am wondering if RStudio is capable of displaying all tables after a schema is typed out. I am new to this. Thanks.

```{sql, output.var = "groups"}

SELECT * FROM Database.schema.
```

You should be able to see the database and its table in the connection pane
https://db.rstudio.com/rstudio/connections/

iinside the Rnotebook, you won't have autocompletion - I don't think it is supported. (thanks @andresrcs!)

Actually, it is, but you need to have an active connection

1 Like

That's what I am looking for. I can't seem to get it to do the same for me. When I type the database name followed by dot and hit the tab key, I get no matches. Did you have to do anything special?

1 Like

I believe you are missing "connection=..." in your chunk options; have a second look at the example above.

1 Like

I tried that and it didn't work.

Can you share the whole code you tried ?
Which package do you use to create the connection object ?

@andresrcs I wonder if the auto completion feature is bound to :package: with connection contract only... :thinking:

```{r setup, include=FALSE}
library(odbc)
con <- dbConnect(odbc(), Driver = "SQL Server", Server = "", 
    trusted_connection = TRUE)
```

```{sql connection = con}
SELECT * FROM database.schema.

```

I am struggling to get the ``` to show so you can assume they are there. Thanks again.

1 Like

Just put 4 of them (one more ) above and under to include your rmarkdown code.

Do you put the connection object ˋcon` as a chunk option in the second chunk ?

Yes, it is, but that doesn't seem to be the problem here, since costhetamax is using the odbc package.

I have found that autocompletion feature is not perfect, it has some quirks, I suspect the problem here is that the connection doesn't specify a database so the sql query start from the database level and not from the schema level.

1 Like

Thanks. I suspected that and it was why I ask the connection to see if odbc was used or not.
I agree it does not seem to be the issue.
Your last assertion could be the reason...

That is annoying...

One more thing you can do is checking the available tables on SQL server level = by running SELECT * FROM INFORMATION_SCHEMA.TABLES; in your SQL chunk; if this select returns a meaningful answer the error should be on RStudio side, if not there is something wrong with your server / connection / rights.

1 Like

The r data(x) command brings in the data table x. If you just type data() it lists all the available tables. Not sure if this works in your particular instance but it's easy enough to try.

Bill Bentley

So what I am gathering here is that autocompletion is not available. Thanks to you all of you.

Autocomplete should be available. This post actually led us to discovering a bug, so thank you for surfacing the issue! The issue can be tracked here.

Basically, autocomplete will work if you remove whitespace from the code chunk header:

```{sql, connection=con}
SELECT * FROM table;
```

Hope this helps! If there are still persistent issues, feel free to report back.

1 Like

I removed the whitespace and tried in both RNotebook and RMarkdown and it didn't work. I am inclined to think I may be doing something wrong.

---
title: "R Notebook"
output: html_notebook
---

```{r}
library(odbc)
con <- dbConnect(odbc(), Driver = "SQL Server", Server = "prodedw2", 
    trusted_connection = TRUE)
```


```{sql, connection=con}
select * from table
```

I tried it on the database, schema and table levels neither worked. I even tested it on a where clause and it still didn't work.

Thanks for your help in advance.