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
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:

#query.columnList#
# 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
The SQL command used to retrieve records. This command syntax can be absolutely oversimplified or extremely entangled. As you turn out to be more OK with database programming, you will figure out how to improve your SELECT statements, at last influencing your database to do however much work as could be expected and not exhausting your programming dialect of choice.
# Posted By Buy an Essay Online | 1/3/18 5:53 AM
SQL deviates in a few routes from its theoretical establishment, the social model and its tuple math. In that model, a table is an arrangement of tuples, while in SQL, tables and inquiry comes about are arrangements of columns.
# Posted By UK Assignment Help | 1/22/18 5:17 AM
SQL proclamations are the methods by which projects and clients get to information in an Oracle database. The areas that take after demonstrate each SQL explanation and its related punctuation. Regardless of the presence of the standard, SQL code isn't totally convenient among various database systems without modifications.
# Posted By Mafia III Jacket | 1/25/18 1:29 AM
This summon punctuation can be completely misrepresented or to a great degree snared. As you end up being all the more OK with database programming, you will make sense of how to enhance your SELECT articulations, finally affecting your database to do however much work as could be normal and not debilitating your programming vernacular of decision.
# Posted By Write My Essay Service | 1/30/18 11:42 PM
This SQL text information is great resourceful information which I like to to have for my learning, as I'm new in learning process which is very effective.
# Posted By Write My Research Paper For Me | 3/5/18 7:41 AM
Our processes, procedures, and policies square measure all targeted around a collection of moral business principles that emphasize honesty, integrity, expertness, and discretion. we have a tendency to believe that a key to making sure the best quality results is facilitating open communication between our purchasers and their writers. So, Essay Corp client portal permits you to speak directly together with your author concerning your assignments.
# Posted By Assignment Writers | 3/6/18 6:47 AM
This Is Really A Great Stuff For Sharing. Thanks For Sharing.
# Posted By Buy Civil Engineering Assignment Help | 6/5/18 2:10 AM
My Friend Recommended This Blog And He Was Totally Right Keep Up The Good Work
I Appreciate Your Efforts In Preparing This Post. I Really Like Your Blog Articles.
# Posted By High Quality Assignment Help | 6/21/18 8:28 AM
Well Thanks For Posting Such An Outstanding Idea. I Like This Blog & I Like The Topic And Thinking Of Making It Right.
# Posted By Help With Audit Homework | 6/21/18 8:42 AM
Good work. I am sure that moving with this syntax, anyone who is capable of writing code on his own, can easily move with the best things.
# Posted By PhD Dissertation Writing Services | 7/17/18 6:06 AM
The all the modern business websites made by these PHP and backend database are totally based on SQL. It is the most common but important platform.
# Posted By Pakistan no 1 seo service provider | 8/18/18 8:06 AM
Amazing blog with inspiring contents. Got so much motivation by just reading the entire content. Interesting from the beginning till the end. Hoping to read more contents of the same kind. Keep posting and keep sharing.
# Posted By How To Extra Money | 9/11/18 5:16 PM
The pages in a CFML application incorporate the server-side CFML labels and capacities notwithstanding HTML labels, and present-day CFML applications additionally have a tendency to have CFCs that are gotten to by the CFML pages for executing business rationale. At the point when an internet browser asks for a page in a ColdFusion application, it is naturally pre-handled by the ColdFusion application server.
# Posted By fuma pod system | 9/29/18 5:43 PM
I am really enjoying reading your well written articles. I think you spend numerous effort and time updating your site. I have bookmarked it and I am taking a look ahead to reading new articles https://www.idolnetworth.com/
# Posted By net worth | 10/9/18 4:08 AM
Pretty Helpful Material, Much Thanks For This Article
# Posted By Make My Accounting Essay Writing Service | 10/25/18 1:21 AM
The Leading Assignment Help UK Firm Offers State Of The Art Services To Its Clients With A Promise Of Delivering All The Required Work Well Within The Deadline.
# Posted By Help With Engineering Project | 10/25/18 6:45 AM
Finance Assignment Help
# Posted By Finance Assignment Help | 2/13/19 4:02 AM
LEDs are made of non-toxic materials, unlike neon lighting that uses mercury that may pose a danger to the environment. LED is recyclable and also considered “green”, or earth-friendly.
# Posted By Camera Lens Bullet Ip | 4/12/19 7:15 AM

© 2019, Stephen J. Withington, Jr.  |  BlogCFC was created by Raymond Camden – Version 5.9.004

Creative Commons License  |  This work is licensed under a Creative Commons Attribution 3.0 Unported License.  |  Hosted by Hostek.com