Retreiving SQL-Server database information

[Pretorian]

With "myQuery.ColumnList", I get the list of columns in a database query.
Using GetMetaData(), I can then read the data type of each column using typeName.
Is there a way to get additional information, such as the length of varchar data types?

[BKBK · Accepted solution]

You can use cfdbinfo. I shall now give you a quick proof-of-concept.

You can improve it using the struct or array functions.

<cfdbinfo 
type="columns" 
table="myTable"
datasource="myDSN" 
name="tableColumnData"> 

<!--- Dump the properties of all the columns of myTable. 'tableColumnData' is a query object. --->
<!---<cfdump var="#tableColumnData#">--->

<!---Get the size of the column named 'myCol'--->
<cfloop index="i" from="1" to="#tableColumnData.recordcount#">
	<cfset columnSize = tableColumnData["column_name"][i]=="myCol" ? tableColumnData["column_size"][i] : "Column not found or column-size cannot be determined.">
	<cfif tableColumnData["column_name"][i]=="myCol">
		<cfbreak>
	</cfif>
</cfloop>

Size of column myCol:<cfoutput> #columnSize#</cfoutput>

[Pretorian]

Sometimes it's that simple.
With "cfdbinfo," of course.
Thanks for the tip.

[BKBK]

My pleasure.

[BKBK]

Here's an array function you may use instead of the loop:

<!---Get the size of the column named 'myCol'--->
<cfset columnIndex = arrayFindNoCase(tableColumnData["column_name"], "myCol") ?: 0>
<cfset columnSize = columnIndex != 0 ? tableColumnData["column_size"][columnIndex] : "Column not found or column-size cannot be determined.">
	
Size of column:<cfoutput> #columnSize#</cfoutput>