Dynamically listing table in MYSQL

Posted on 2009-02-17
Last Modified: 2012-06-27
Hi well i am running this query in <cfquert tag as:

SHOW FULL tables

the above is run in <cfquery> to get all the tables from mysql. database

ok when i use CFDUMP it comes as:


Now when i use another database i have to hard code the databaseName to get all tables like:


I tried something like this:

<cfset dbName = listGetAt(list.columnList,3,"_")>

to get the databaseName dynamically and get the tablesName exactly but it is not working as though.

Please help
<cfoutput query="list">


          <td><cfset dbName = listGetAt(list.columnList,3,"_")>




Open in new window

Question by:myselfrandhawa
    LVL 27

    Expert Comment

    try attached code - not sure if it does exactly what you need since i am not sure i correctly understood your multiple databases querying part: is the datasource name you provide to your <cfquery> tag base on some [user-selected] variable?

    <cfquery name="list" datasource="#some_variable_here_i_guess#">
    <cfset dbname = ucase(listlast(listfirst(list.columnlist), "_"))>
    in my test on MySQL database the column names returned by LIST query were always 'TABLES_IN_[DB_NAME_HERE]' and 'TABLE_TYPE', thus 'TABLES_IN_...' column is always the first element in the COLUMNLIST list - hence used listfirst() in the code above
    <!--- now use array notation to output rows of table names --->
    <cfoutput query="list">
    #list['TABLES_IN_' & dbname]#<br />

    Open in new window

    LVL 15

    Author Comment

    the datasource is dyanmic as datasource is stored in session and based on that session. if datasource is mysql

    then the query will run based on the datasource selected.

    Here is what i tried and get this result:

    <cfoutput query="list">
              <td><cfif session.dsntype eq 'mysql'>
                          <cfset dbname = ucase(listlast(listfirst(list.columnlist), "_"))>
                                  #list['TABLES_IN_' & dbname]#<br />
                  Blah Blah Blah

     The web site you are accessing has experienced an unexpected error.
    Please contact the website administrator.

    The following information is meant for the website developer for debugging purposes.
    Error Occurred While Processing Request
    Complex object types cannot be converted to simple values.
    The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
    LVL 27

    Expert Comment

    another ooops on my part! (too much beer i guess...)

    change this:
    #list['TABLES_IN_' & dbname]#

    to this:
    #list['TABLES_IN_' & dbname][currentrow]#
    LVL 27

    Accepted Solution

    also, just fyi, SHOW FULL TABLES command is MySQL-specific - so no need for that dbtype chack you have there.

    for commands to use on other rdbms:


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now