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
<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
<cfset MSSQLColumns = ValueList(rsMSSQLColumns.column_name)>



If you want to know the columns in a query you already used but don't want to run a separate query to find out:

# Posted By Jules Gravinese | 7/1/11 11:59 AM
Yep, and I wrote a blog post awhile back about using CFDBInfo as another option: http://www.stephenwithington.com/blog/index.cfm/20...
# Posted By Steve Withington | 7/1/11 12:15 PM

© 2024, Stephen J. Withington, Jr.  |  Hosted by Hostek.com

Creative Commons License   |   This work is licensed under a Creative Commons Attribution 3.0 Unported License.