Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Retreiving SQL-Server database information

New Here ,
May 20, 2025 May 20, 2025

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?

177
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , May 21, 2025 May 21, 2025

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 = tableColum
...
Translate
Community Expert ,
May 21, 2025 May 21, 2025

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>
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 22, 2025 May 22, 2025

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 22, 2025 May 22, 2025

My pleasure.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 22, 2025 May 22, 2025
LATEST

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>
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources