getting the tablename inside the select

i am currently using to get all the tablenames from mysql database

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

but i want to list all the tables which have a starting name of scriptasof

The thing is above works if i do not specify the like attribute but when i apply like attribute, it fails

because my tables names are as

scriptasof
scriptasof_20_11_2011
scriptasof_21_11_2011
scriptasof_22_11_2011
scriptasof_23_11_2011

basically i want to extract the date which is 20_11_2011 from the table names starting with scriptasof so i can list these all dates in a select statement, so please guide

Thanks
LVL 16
Gurpreet Singh RandhawaWeb DeveloperAsked:
Who is Participating?
 
Rahul GadeSr. ArchitectCommented:

You may want to use something like, just make sure that collate of your information_schema database and your database is some, otherwise you will also need to apply conversion.

SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_name like 'scriptasof%' ORDER BY table_name DESC;


-Rahul Gade
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.