How to extract create statements from different tables of MySQL DBs?

Question

I would like to extract all Create Statements in my 50 MySQL Databases via SHOW CREATE TABLE db.table or SHOW CREATE TABLE db1.mytableor SHOW CREATE TABLE db2.sometableor SHOW CREATE TABLE db3.mytable1. Thus each of the DBs has some tables inside db1(table,mytable...) db2(table1,sometable) and so on

To illustrate the DBs via a example query:

SELECT *
FROM db.table1 m
   LEFT JOIN db1.sometable o ON m.id = o.id
   LEFT JOIN db2.sometables t ON p.id=t.id
   LEFT JOIN db3.sometable s ON s.column='john'


library(RMySQL)
library(DBI)

con <-  dbConnect(RMySQL::MySQL(), 
                    username = "", 
                    password = "",
                    host = "", 
                    port = 3306,
                    dbname= mydbname)#  when using dbs<-dbGetQuery(con ,"SHOW DATABASES") I have to ## dbname= mydbname## to get all DBs 

Using dbs<-dbGetQuery(con ,"SHOW DATABASES")I can extract all 50 Databases in the dbConnection as character vector. I would like loop over each DB in the dbsand apply SHOW CREATE TABLE to each row/db. I suppose I have to parse the each row/db into dbname= mydbnameand dbs<-dbGetQuery(con ,"SHOW CREATE TABLE"). But I just cant figure out how to make the loops

I tried:

apply(dbs, 1, function(row) {
      dbname <- row[]
      for (i in 1:length(dbname)) {

        create<-dbGetQuery(con,"SHOW CREATE TABLE") }

    })

But that doesnt seem right. I suppose I have to include the con into the loop somehow. Otherwise I'll get: Error in .local(drv, ...) : object 'dbname' not found

So I tried:

apply(dbs, 1, function(row) {
      dbname <- row[]
      for (i in 1:length(dbname)) {
                    con <-  dbConnect(RMySQL::MySQL(), 
                    username = "", 
                    password = "",
                    host = "", 
                    port = 3306,
                    dbname= [i])
        create<-dbGetQuery(con,"SHOW CREATE TABLE") }})

I suppose that comes close to the solution but I miss something:

dbs<-dbGetQuery(con,"show databases")

library(foreach)

foreach(i = 1:(length(dbs))%dopar%{
  query<-paste("SHOW CREATE TABLE",dbs[i])
  creates<-dbGetQuery(con,query)
})

Answer 1

Consider this approach of importing a data frame of each database (leaving out the system ones, INFORMATION_SCHEMA and MYSQL) and their corresponding tables. Then, run SHOW CREATE TABLE statements. Finally, merge the original dataframe with binded dataframe of create statements.

Now, the one caveat is tables that repeat names across databases. To return distinct values of such combinations, the aggregate() by head function is used.

con <-  dbConnect(RMySQL::MySQL(),
                  username = "****", password = "****",
                  host = "****", port = 3306,
                  dbname= "****")
dbtbls <- dbGetQuery(con, "SELECT `TABLE_SCHEMA` AS `Database`, 
                                  `TABLE_NAME` AS `Table`
                            FROM  `INFORMATION_SCHEMA`.`TABLES` 
                            WHERE `TABLE_TYPE` = 'BASE TABLE'
                              AND `TABLE_SCHEMA` NOT LIKE '%SCHEMA%' 
                              AND `TABLE_SCHEMA` NOT LIKE '%MYSQL%' ")
# LIST OF SQL STATEMENTS
sql <- paste0("SHOW CREATE TABLE ", dbtbls$Database, ".", dbtbls$Table)

# LIST OF DATAFRAMES
createstmts <- lapply(sql, function(x) dbGetQuery(con, x))
dbDisconnect(con)

# ROW BIND LIST INTO ONE DATAFRAME TO MERGE WITH ORIGINAL    
stmtsdf <- do.call(rbind, createstmts)
finaldf <- merge(dbtbls, stmtsdf, by='Table')

# RETURN DISTINCT RECORDS
finaldf <- aggregate(.~Database+Table, finaldf, FUN=head, 1)