Dynamically Getting MySQL Table Names 1

[Wullie]

Hi all,

I'm trying to loop through multiple MySQL databases to get the names of every table in them and I'm hitting a problem that probably has a simply solution but I need another set of eyes to check.

I have more code than below however this is the stripped down version of where I am hitting the problem.

<cfquery name="qdb" datasource="#application.dsn#" username="#application.user#" password="#application.pass#">
SHOW DATABASES
</cfquery>

<cfloop query="qdb">

<cfquery name="qtable" datasource="#application.dsn#" username="#application.user#" password="#application.pass#">
SHOW TABLES FROM #DATABASE#
</cfquery>

<cfoutput query="qtable">

<!--- PROBLEM AREA --->

</cfoutput>

</cfloop>

Up until the <!--- PROBLEM AREA ---> section, everything is fine and returns as expected. However, MySQL returns the list of tables as #tables_in_dbname#, where dbname is the name of the database. Because I am running this against multiple databases, I can't hard code this variable.

I'm basically looking to do the equivelent of #tables_in_#qdb.database##, however obviously that won't work but it's the easiest way I can think of to explain.

Any suggestions are welcome, even if it means rewriting the whole thing.

Wullie

Fresh Look - Quality Coldfusion/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[r937]

yes, the column name comes back as Tables_in_yourdb

but i don't understand the problem

what do you need to use the columnname for?

why can't you pull it off the end of qtable.ColumnList?



r937.com | rudy.ca

 

[Wullie]

Thanks for your reply.

I am trying to get the name of every table from each database. When I run the query to get the database names, I then run a query based on those to get the table names and I am having a problem outputting these.

The problem is when I need to output the names of the tables, the variable I need to use has to contain the name of the DB, so for instance to get the tables on the database TEST, I need to use the variable #tables_in_test#. Because this variable will change as it runs against the next database, I have no way to dynamically build the variable so it contains the database name.

The whole script is basically to backup every table separately, rather than just the full database. To do this however I need to have a list of all the table names so that I can run the backup against each one.

Hope this clarifies the issue,

Wullie

Fresh Look - Quality Coldfusion/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[r937]

this works for me, except i only have one database, so i'm not completely certain it will work for yoiu

however, it does actaully list my tables correctly

<cfquery name="qdb" ... >
 show databases
</cfquery>

<cfloop query="qdb">
  <cfset whichdb = qdb.Database>
  <h2>Database: 
    <cfoutput>#whichdb#</cfoutput></h2>
  <cfquery name="qtable" ... >
   show tables from #whichdb#
  </cfquery>
  <p>
  <cfset whichcolumn 
     = 'qtable.Tables_in_' & whichdb >
  <cfoutput query="qtable">
    #Evaluate(whichcolumn)#<br>
  </cfoutput>
  </p>
</cfloop>

r937.com | rudy.ca

 

[Wullie]

Rudy,

You are a saviour! Many thanks, I would have been here all night going round in circles.

Wullie

Fresh Look - Quality Coldfusion/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[r937]

now you can watch the steelers and chargers

r937.com | rudy.ca

 

[Wullie]

I'm from the UK, so it means I can clear some of my 'to do' list done before the sun comes up.

Thanks again.

Wullie

Fresh Look - Quality Coldfusion/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell