Skip to main content
Inspiring
March 4, 2010
Question

Trying to list table column names with data type

  • March 4, 2010
  • 3 replies
  • 668 views

I have a list of table names and need to output the fields and their type.  So I tried the code below but getName() returns string even when I know the field is an int.  So I'm assuming I'm using the wrong function.  I looked at the output from getMethods() but I didn't see anything else that would return the information I want.  TIA.

<cfset TableList = "accountingsettings,address,area,areasettings">

<cfloop from="1" to="#Listlen(TableList)#" index="i">
<cfquery datasource="axxerion" name="getTablename">
select * from #Listgetat(TableList, i)#
where 0<>0
</cfquery>

<cfset colHeaderNames = ArrayToList(getTablename.getColumnList())/>
<cfoutput>
<p><b>#Listgetat(TableList, i)# #getTablename.getMetaData().getColumnCount()#</b></p>
<ul>
<cfloop from="1" to="#Listlen(colHeaderNames)#" index="x">
<cfdump var="#getMetaData(Listgetat(colHeaderNames,x)).getmethods()#">
<li>#Listgetat(colHeaderNames,x)# #getMetaData(Listgetat(colHeaderNames,x)).getName()# </li>
</cfloop>
</ul>
</cfoutput>
</cfloop>

Returns

accountingsettings 22

  • ID java.lang.String
  • BINENTRYID java.lang.String
  • CLIENTID java.lang.String
  • CREATESYSTEMUSERID java.lang.String
  • CREATETIME java.lang.String
  • DEPRECIATIONYEARS java.lang.String
  • POSTNOTAPPROVED java.lang.String
  • REGIONID java.lang.String
  • RESIDUALVALUE java.lang.String
  • REVISION java.lang.String
  • UPDATESYSTEMUSERID java.lang.String
  • UPDATETIME java.lang.String
  • DUPLICATEREFERENCES java.lang.String
  • STRICTMODE java.lang.String
  • CONSOLIDATEINVOICEITEMS java.lang.String
  • AUTOACCOUNTINGPERIOD java.lang.String
  • INVOICEDESCRIPTION java.lang.String
  • POSTREQUIRED java.lang.String
  • CONSOLIDATEOUTINVOICEITEMS java.lang.String
  • PAYMENTDIFFERENCECATALOGITEMID java.lang.String
  • ACCOUNTINGPERIODLENGTHCODE java.lang.String
  • CREDITPAYMENTS java.lang.String
This topic has been closed for replies.

3 replies

kiyomiAuthor
Inspiring
March 9, 2010

Thanks, I don't know why I was trying to make this so difficult.  I queried the information_schema and got exactly what I wanted quite easily...

Inspiring
March 4, 2010

So I'm assuming I'm using the wrong function. 

You are using GetMetaData() incorrectly. It returns an array of structures. Cfloop through the returned array, and output the #name# and #typeName# keys.

But .. if you only need the column metadata. Dan's suggestion of using cfdbinfo would seem more appropriate here.

Inspiring
March 4, 2010

If you have permission, you can query the system tables to get the info you need.  Or, I have vague recollections of something called dbinfo in coldfusion but don't remember the details.