SQL Syntax To Get Columns From Database

Posted At : July 1, 2011 10:54 AM | Posted By : Stephen Withington
Related Categories: ColdFusion,SQL

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

<cfquery name="rsMySQLColumns" datasource="MyDSN">
SHOW COLUMNS from MyTable
</cfquery>
<cfset MySQLColumns = ValueList(rsMySQLColumns.Field)>

MS SQL Syntax

<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 (43) |

Print |

Send |

Comments

[Add Comment]

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

# Posted By structured settlement companies in boca raton fl | 6/5/18 4:03 AM

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

Buying a home involves time, energy, and, most of all, money. In addition to committing yourself to mortgage payments for 15 to 30 years, you need quite a bit of money “up-front” to close the transaction that will make the house yours.

# Posted By Fannie Mae's small apartment loan program | 11/6/19 6:26 AM

nice post, keep up with this interesting work. It really is good to know that this topic is being covered also on this web site so cheers for taking time to discuss this!

# Posted By talk to strangers | 11/26/19 8:19 AM

Thankyou for this wondrous post, I am glad I observed this website on yahoo.

# Posted By talk to strangers | 11/26/19 11:42 PM

Today, I was just browsing along and came upon your blog. Just wanted to say good blog and this article helped me a lot, due to which I have found exactly I was looking.

# Posted By talk to strangers | 11/27/19 9:23 PM

Attractive, post. I just stumbled upon your weblog and wanted to say that I have liked browsing your blog posts. After all, I will surely subscribe to your feed, and I hope you will write again soon!

# Posted By talk to strangers | 12/1/19 1:28 AM

There is a lot going on in the latest article that I just read on altoprotectpdf.com. The writer has done a commendable job with it, it is by far the best article on that site. And I have read almost every post and article there.

# Posted By altoprotectpdf.com | 12/6/19 4:42 AM

Hello I am so delighted I located your blog, I really located you by mistake, while I was watching on google for something else, Anyways I am here now and could just like to say thank for a tremendous post and a all round entertaining website. Please do keep up the great work.

# Posted By talk to strangers | 12/8/19 1:12 AM

I have recently started a blog, the info you provide on this site has helped me greatly. Thanks for all of your time & work.

# Posted By https://www.forbes.com/sites/katiechang/2016/09/20 | 12/15/19 11:18 PM

The Department of Andhra Pradesh Public Service Commission (APPSC) is going to Conduct the Group-III Services Examination in the year 2020. Candidates who are dreaming about the Group 3 Services for the Panchayat Secretary (Group-IV) have to start preparing immediately because there is huge competition for the PSC Jobs in AP. Applicants who are preparing for the Andhra Pradesh PSC Examination, they have to check the Exam Pattern and APPSC Panchayat Secretary Syllabus on our site. Andhra Pradesh PSC Group 3 Exam Pattern and APPSC Panchayat Raj Secretary Syllabus are updated here for the people who are searching it.

# Posted By APPSC Group 3 Syllabus | 1/8/20 1:38 AM

Thanks for an interesting blog. What else may I get that sort of info written in such a perfect approach? I have an undertaking that I am just now operating on, and I have been on the lookout for such info.

# Posted By https://caldwells.com/ | 5/10/20 12:12 PM

I recorded this article, a resolution of gratefulness is all Mumbai accompanies all things considered for sharing this content. This is inconceivably supporting for one and all and marvelous.
http://www.aliyamumbaiescort.in/

# Posted By Mumbai escorts | 8/19/20 2:21 AM

I recently found much useful information on your website especially this blog page.
http://www.lucknowescorts.co.in/alambagh-escorts/
http://www.lucknowescorts.co.in/gomti-nagar-escort...
http://www.lucknowescorts.co.in/kesar-bagh-escorts...
Among the lots of comments on your articles. Thanks for sharing.

# Posted By rasmika chopra | 11/5/20 2:46 AM

The Knovva Academy understands the need for online high school classes, as is all set to combat the post-pandemic circumstances by the virtual model G20 summit.

# Posted By Experiential Learning | 11/27/20 12:51 PM

I think this is one of the most significant info for me. And i'm glad reading your article. But should remark on some general things, The site style is perfect, the articles is really nice : D. Good job, cheers

# Posted By abstract art paintings | 4/16/21 7:40 PM

It's amazing place together with I like to read the paper the place. your blog is certainly super also, you own superior workforce in your own webpage.

# Posted By Salah Time | 10/5/21 9:06 AM

I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post.

# Posted By Net Worth | 11/27/21 10:57 PM

giving in anything short of a positive, certain way with an in everyday typical individual, will not basically sway deals execution.

# Posted By escort services delhi | 3/4/22 3:36 AM

This article gives the light in which we can observe the reality. This is very nice one and gives indepth information.

# Posted By Bangalore Escorts | 9/17/22 6:25 AM

Thanks for sharing this information. I really like your blog post very much. You have really shared a informative and interesting blog post with people.

# Posted By Bangalore Escorts | 9/17/22 6:26 AM

This is very interesting article.

# Posted By Escorts in Bangalore | 9/17/22 6:26 AM

Thanks for sharing this interesting article with lots of information.

# Posted By Escorts in Bangalore | 9/17/22 6:27 AM

Your articles are to a stunning degree drawing in and I got a goliath level of data and heading sorting out them Overwhelming Site.
https://www.dlfgurgaoncallgirls.com/escorts-35-sta...

# Posted By Gurgaon Russian Escorts Service | 9/26/22 1:13 AM

[Add Comment]