how to loop over the tables of a database?

Question

I am trying to loop over the tables of a database using the following code getting an error java.lang.UnsupportedOperationException. i have even tried with cfloop query and other attributes got errors complex value can't convert to simple value. can anyone tell me how shall i have to loop over this query ? Thanks.

<cfquery name="q" datasource="datasource">
    SHOW TABLES FROM datasource
</cfquery>
<cfloop collection ="#q#" item="i">
   #q[i]#
</cfloop> 

Answer 1

You are getting that error because cfloop collection expects a structure, not a query object. Hence the "UnsupportedOperation..." error.

Instead you should use a query loop. The generated column name is dynamic, based on the database name you supply. You can either hard code it or access it dynamically:

   <cfset colNames = listToArray(q.columnList)>
   <cfoutput query="q">
      <cfloop array="#colName#" index="col">
            #q[col][currentRow]#
      </cfloop>
      <br>
   </cfoutput>

That said, I find it easier to use the metadata INFORMATION_SCHEMA views. You can query them just like any table. Then output the static column names as usual.

    <cfquery name="yourQueryName" ...>
       SELECT  TABLE_NAME
       FROM    INFORMATION_SCHEMA.TABLES
       WHERE   TABLE_SCHEMA = 'YourDatabaseName'
    </cfquery>

    <cfoutput query="yourQueryName">
         #TABLE_NAME# <br>
    </cfoutput>