Coldfusion Query tablelist from multiple databases and bind them in a form.
Using the meta data available in most database software you can usually get a list of tables by querying the sysObjects table. However I had need to change the list of tables based on a database name selected. We have multiple databases on our server. So here is the code. It uses Coldfusion and a little Javascript while querying the system resources. This is for Sybase and may need adjustment for Oracle or MSSQL database. Not quite as elegant as what Ben Forta might write but it gets the job done.
Main CFM
<cfquery name=\"get_datasources\" datasource=\"yourDS\">
select distinct name as datasource_name_v from master..sysdatabases
</cfquery>
<cfform name=\"sform\" id=\"sform\" action=\"docs_main.cfm\" method=\"post\" >
<label for=\"datasource_name\">Datasource Name</label>
<cfselect name=\"datasource_name\">
<cfoutput><option value=\"#datasource_name_r#\">#datasource_name_r#</option></cfoutput><br />
<cfoutput query=\"get_datasources\">
<option value=\"#trim(datasource_name_v)#\">#trim(datasource_name_v)#</option>
</cfoutput>
</cfselect>
<label for=\"table_name\">Table Name</label>
<cfselect name=\"table_name\">
<cfoutput><option value=\"\">Select a table name</option></cfoutput><br />
</cfselect>
...
</cfform>
<em><strong>bind the datasource name</strong> </em>
<cfdiv bind=\"url:mod_get_tables.cfm?cfdebug&datasource_name={datasource_name@change}\" />
mod_get_tables.cfm (the include file which performs the magic)
<cfparam name=\"url.datasource_name\" default=\"\">
<cfif #len(trim(url.datasource_name))# gt 0>
<!---Query sysobjects for the selected database name to get tables --->
<cfquery name=\"get_tables\" datasource=\"your_dsn\">
set rowcount 0
select name as table_name_v
from #url.datasource_name#..sysobjects
where type <> 'S' and name <> ''
order by name
</cfquery>
<script language=\"javascript\" type=\"text/javascript\">
// get rid of all but top line incase it is populated from the database
document.sform.table_name.options.length= 1;
//get the number of records output
var rc = <cfoutput>#get_tables.recordCount#</cfoutput>;
<cfset rcx = #get_tables.recordCount#>
//set up a dummy counter
</cfset><cfset j=1>
//define the number of records for the select option list
document.sform.table_name.options.length= rc;
//loop over the query and output the variables to the option list.
<cfloop query=\"get_tables\" startRow=\"1\" endRow=\"#rcx#\" >
<cfoutput>
document.sform.table_name.options[#j#] = new Option(\"#table_name_v#\", \"#table_name_v# \", false, false)
<cfset j= #j# + 1>
</cfoutput>
</cfloop>
</script>
<cfelse>
<cfset table_name_v = \"no datasource selected\">
</cfset></cfelse></cfif>
</cfparam></pre></code>