How do I find the schema of a table in an ODBC connection by name?How to check if a column exists in a SQL Server table?Table Naming Dilemma: Singular vs. Plural NamesWhat is the best way to auto-generate INSERT statements for a SQL Server table?Find all tables containing column with specified name - MS SQL Servertnsping resolves but not able to connect using scriptCannot connect to data warehouse using ODBC connectionHow to get the schema of a database with the DBI packageHow to get the SQL Server name and the other arguments of odbcConnect function in RODBC package?Trouble accessing recently created ODBC tableHow to use DSN file for connection to SQL Server using R
Half filled water bottle
Weird corners with cline
Talk interpreter
Is a Centaur considered an animal when calculating carrying capacity for vehicles?
Redacting URLs as an email-phishing preventative?
Count the number of shortest paths to n
How can I download a file from a host I can only SSH to through another host?
To what extent should we fear giving offense?
Should open strings on guitar be tuned to the key of the song played?
Why is getting a PhD considered "financially irresponsible" by some people?
Where does learning new skills fit into Agile?
Is it ok to record the 'environment' around my workplace?
Why is strlen so complex in C?
What are the IPSE’s, the ASPE’s, the FRIPSE’s and the GRIPSE’s?
Disk usage of integer column vs boolean column in Postgres
What does it take for witness testimony to be believed?
Given current technology, could TV display screens double as video camera sensors?
How is linear momentum conserved in case of a freely falling body?
Is it legal for source code containing undefined behavior to crash the compiler?
How does the OS tell whether an "Address is already in use"?
What is this fighter jet at Weymouth NAS?
Why does a sticker slowly peel off, but if it is pulled quickly it tears?
Did Dr. Hannibal Lecter like Clarice or was he attracted to her?
How to say "I only speak one which is English." in French?
How do I find the schema of a table in an ODBC connection by name?
How to check if a column exists in a SQL Server table?Table Naming Dilemma: Singular vs. Plural NamesWhat is the best way to auto-generate INSERT statements for a SQL Server table?Find all tables containing column with specified name - MS SQL Servertnsping resolves but not able to connect using scriptCannot connect to data warehouse using ODBC connectionHow to get the schema of a database with the DBI packageHow to get the SQL Server name and the other arguments of odbcConnect function in RODBC package?Trouble accessing recently created ODBC tableHow to use DSN file for connection to SQL Server using R
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I'm using the odbc package to connect to a MS SQL Server
con <- dbConnect(odbc::odbc(),
Driver = "ODBC Driver 13 for SQL Server",
Server = "server",
Database = "database",
UID = "user",
PWD = "pass",
Port = 1111)
This server has many tables, so I'm using dbListTables(con) to search for the ones containing a certain substring. But once I find them I need to discover which schema they are in to be able to query them. I'm currently doing this manually (looking for the name of the table in each schema), but is there any way I can get the schema of all tables that match a string?
r
sql-server odbc rodbc r-dbi asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
add a comment |
I'm using the odbc package to connect to a MS SQL Server
con <- dbConnect(odbc::odbc(),
Driver = "ODBC Driver 13 for SQL Server",
Server = "server",
Database = "database",
UID = "user",
PWD = "pass",
Port = 1111)
This server has many tables, so I'm using dbListTables(con) to search for the ones containing a certain substring. But once I find them I need to discover which schema they are in to be able to query them. I'm currently doing this manually (looking for the name of the table in each schema), but is there any way I can get the schema of all tables that match a string?
r
sql-server odbc rodbc r-dbi asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
add a comment |
I'm using the odbc package to connect to a MS SQL Server
con <- dbConnect(odbc::odbc(),
Driver = "ODBC Driver 13 for SQL Server",
Server = "server",
Database = "database",
UID = "user",
PWD = "pass",
Port = 1111)
This server has many tables, so I'm using dbListTables(con) to search for the ones containing a certain substring. But once I find them I need to discover which schema they are in to be able to query them. I'm currently doing this manually (looking for the name of the table in each schema), but is there any way I can get the schema of all tables that match a string?
r
sql-server odbc rodbc r-dbi asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
I'm using the odbc package to connect to a MS SQL Server
con <- dbConnect(odbc::odbc(),
Driver = "ODBC Driver 13 for SQL Server",
Server = "server",
Database = "database",
UID = "user",
PWD = "pass",
Port = 1111)
This server has many tables, so I'm using dbListTables(con) to search for the ones containing a certain substring. But once I find them I need to discover which schema they are in to be able to query them. I'm currently doing this manually (looking for the name of the table in each schema), but is there any way I can get the schema of all tables that match a string?
r
sql-server odbc rodbc r-dbi r
sql-server odbc rodbc r-dbi asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
edited Mar 28 at 13:40
Fino
asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
asked Mar 27 at 19:56
FinoFino
1,1305 silver badges16 bronze badges
1,1305 silver badges16 bronze badges
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Consider running an SQL query with LIKE search using the built-in INFORMATION_SCHEMA metadata table if your user has sufficient privileges.
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%some string%'
Call above with R odbc with a parameterized query on the wildcard search:
# PREPARED STATEMENT
strSQL <- paste("SELECT SCHEMA_NAME" ,
"FROM INFORMATION_SCHEMA.SCHEMATA",
"WHERE SCHEMA_NAME LIKE ?SEARCH")
# SAFELY INTERPOLATED QUERY
query <- sqlInterpolate(conn, strSQL, SEARCH = '%some string%')
# DATA FRAME BUILD FROM RESULTSET
schema_names_df <- dbGetQuery(conn, query)
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55385483%2fhow-do-i-find-the-schema-of-a-table-in-an-odbc-connection-by-name%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Consider running an SQL query with LIKE search using the built-in INFORMATION_SCHEMA metadata table if your user has sufficient privileges.
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%some string%'
Call above with R odbc with a parameterized query on the wildcard search:
# PREPARED STATEMENT
strSQL <- paste("SELECT SCHEMA_NAME" ,
"FROM INFORMATION_SCHEMA.SCHEMATA",
"WHERE SCHEMA_NAME LIKE ?SEARCH")
# SAFELY INTERPOLATED QUERY
query <- sqlInterpolate(conn, strSQL, SEARCH = '%some string%')
# DATA FRAME BUILD FROM RESULTSET
schema_names_df <- dbGetQuery(conn, query)
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
add a comment |
Consider running an SQL query with LIKE search using the built-in INFORMATION_SCHEMA metadata table if your user has sufficient privileges.
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%some string%'
Call above with R odbc with a parameterized query on the wildcard search:
# PREPARED STATEMENT
strSQL <- paste("SELECT SCHEMA_NAME" ,
"FROM INFORMATION_SCHEMA.SCHEMATA",
"WHERE SCHEMA_NAME LIKE ?SEARCH")
# SAFELY INTERPOLATED QUERY
query <- sqlInterpolate(conn, strSQL, SEARCH = '%some string%')
# DATA FRAME BUILD FROM RESULTSET
schema_names_df <- dbGetQuery(conn, query)
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
add a comment |
Consider running an SQL query with LIKE search using the built-in INFORMATION_SCHEMA metadata table if your user has sufficient privileges.
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%some string%'
Call above with R odbc with a parameterized query on the wildcard search:
# PREPARED STATEMENT
strSQL <- paste("SELECT SCHEMA_NAME" ,
"FROM INFORMATION_SCHEMA.SCHEMATA",
"WHERE SCHEMA_NAME LIKE ?SEARCH")
# SAFELY INTERPOLATED QUERY
query <- sqlInterpolate(conn, strSQL, SEARCH = '%some string%')
# DATA FRAME BUILD FROM RESULTSET
schema_names_df <- dbGetQuery(conn, query)
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
Consider running an SQL query with LIKE search using the built-in INFORMATION_SCHEMA metadata table if your user has sufficient privileges.
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%some string%'
Call above with R odbc with a parameterized query on the wildcard search:
# PREPARED STATEMENT
strSQL <- paste("SELECT SCHEMA_NAME" ,
"FROM INFORMATION_SCHEMA.SCHEMATA",
"WHERE SCHEMA_NAME LIKE ?SEARCH")
# SAFELY INTERPOLATED QUERY
query <- sqlInterpolate(conn, strSQL, SEARCH = '%some string%')
# DATA FRAME BUILD FROM RESULTSET
schema_names_df <- dbGetQuery(conn, query)
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
answered Mar 28 at 14:10
ParfaitParfait
61.1k10 gold badges56 silver badges76 bronze badges
61.1k10 gold badges56 silver badges76 bronze badges
add a comment |
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55385483%2fhow-do-i-find-the-schema-of-a-table-in-an-odbc-connection-by-name%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
