SQL Syntax To Get Columns From Database
Have you ever wanted a simple way to get the column names of a database table, but just didn't know how to go about doing it? I've seen this question come up from time-to-time in the wild and thought I would share my simple solution using straightforward SQL syntax.
Obviously, most programming languages offer methods to obtain this information as well, however I thought this might be helpful to ColdFusion/CFML developers or any other SQL users who are looking for a quick and painless way to get what they need.
MySQL Syntax
<!--- MySQL syntax to get column names --->
<cfquery name="rsMySQLColumns" datasource="MyDSN">
SHOW COLUMNS from MyTable
</cfquery>
<cfset MySQLColumns = ValueList(rsMySQLColumns.Field)>
MS SQL Syntax
<!--- MS SQL syntax to get column names --->
<cfquery name="rsMSSQLColumns" datasource="MyDSN">
SELECT column_name,*
FROM information_schema.columns
WHERE table_name = 'MyTable'
ORDER BY ordinal_position
</cfquery>
<cfset MSSQLColumns = ValueList(rsMSSQLColumns.column_name)>
Cheers!
Comments
#query.columnList#