I want to get a list of all the objects on my SQL-Server using R.
I use this code below to import the object "store", but now I want a list with all objects in order to document it...
Butik <- sqlQuery(db_conn, "SELECT * FROM dbo.store")
Asked
Active
Viewed 27 times
0
r2evans
- 141,215
- 6
- 77
- 149
Sven Johansson
- 19
- 2
-
1`DBI::dbListTables(db_conn)` will give you all tables/views, including many system tables/views that may make it difficult to extract just user tables. `dbGetQuery(db_conn, "select * from information_schema.tables where table_type='BASE TABLE'")` will give all table names, no views or system tables (the use of `information_schema.*` is discouraged by some, there are better *albeit more verbose* methods). – r2evans Jan 07 '22 at 12:37
-
BTW, those are using `DBI` with the `odbc` driver package, not using `RODBC` as I suspect you are using. I believe `RODBC::sqlTables` should work for the first, and `sqlQuery(...)` should work for the second. – r2evans Jan 07 '22 at 12:39
-
Possible dupe: https://stackoverflow.com/q/31521908/3358272 – r2evans Jan 07 '22 at 12:40
-
Perfect, many thanks! – Sven Johansson Jan 07 '22 at 23:22