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!

Please Help Me Run in Chicago to Help Mitchell Swaback Charities

Shortly after becoming the lead web developer for Ignition Studio in early 2007, I was introduced to one of our clients, Bob Swaback and Mitchell Swaback Charities. I don't care who you are, or what your background is...if all you do is read the story of Bob and Gaye's son Mitch and why this organization even exists today...you can't help but be inspired. If you continue to read his journal entries, the testimonials, and the lives that have been touched by this group, you will be moved. I've watched from the sidelines; posting story after story, reading everyones comments, posting new projects, events, and all of the updates, pictures and videos that go along with them. I've attended the annual golf outings and dinners and listened as friends and family shared their memories of Mitch. I've helped put together the monthly e-blasts...and yes, even the ones on Mitch's Anniversary.

Isn't it beautiful though, to see all of the great things that have been done with the loss of this young man?  It's painful...and beautiful...all at the same time.

However, the impetus for me to run the half marathon this year goes back to that first year I became involved. In 2007, MSC had a similar fundraiser where several people signed up to run either the half of full marathon in Chicago. Mitch's dad, Bob, committed to running both!  Together, they raised nearly $100,000 that year. At that time, I was nearly sixty pounds heavier and wouldn't have been able to run around the block without taking a break...trust me, I tried.  I told my coworkers at Ignition Studio that if MSC would do this fundraiser again, I would run...well, here I am.

I might not be ready to run the full thirteen miles yet, but with the help of my son Parker, my daugher Meredith and all of my family, friends and supporters....I'm gonna run...one step at a time.

Please consider supporting me with either a kind word, or even a token donation to Mitchell Swaback Charities by sponsoring my run.

If you're interested, please feel free to follow my training activites and updates at http://twitter.com/RunWithSteve

Thank you.

Beware of Implicit Structs Bug in ColdFusion 9,0,1

This has been driving me nuts and I'm not sure how well documented this is, but I've stumbled across a bug in ColdFusion 9,0,1,274733 where implicit structs can end up as 'undefined' and crash your application.

I was working through an example from jQuery in Action, Second Edition (you know, because I was trying to figure something else out) when this happened to me again and I thought I would go ahead and share a simple way for others to test and let me know if it's happening to them.

Create a page called test.cfm and drop this code in it, point your browser to it and you'll probably see an error of 'Variable TERMS is undefined'.


<cfscript>
    if ( not IsDefined('term') ) {
        term = 'unknown';
    };
    term = lcase(term);

    terms = {
        'oil-tanned' = 'A method of leather tanning in which oils or fats are used to cure the leather. Such leather is usually very supple and has a matte or "oily" finish and is not generally polishable.',
        'full-grain' = 'Leather which has not been altered beyond hair removal. Full-grain leather is the most genuine type of leather, as it retains all of the original texture and markings of the original hide.',
        'vibram' = 'A brand of boot and shoe sole created by Vitale Bramani in the 1930s, orginally for climbing boots. They are easily identified by the distinctive yellow Vibram® octagon. The Vibram® brand is recognized worldwide as the leader in high performance soling products for outdoor, dress casual, and service footwear.',
        'goodyear welt' = 'The Goodyear welt is a method of attaching the sole of a shoe to the upper that is hand-stitched and allows multiple sole replacements, extending the life of the footwear.',
        'cambrelle' = ' A non-woven synthetic fabric used primarily as a lining for shoes, boots and slippers.',
        'cordura' = 'A certified fabric from INVISTA used in a wide range of products from luggage and backpacks to boots, to military wear and performance apparel. Cordura® is resistant to abrasions, tears and scuffs.',
        'gore-tex' = 'A water-proof and breathable fabric that offers superior insulating abilities in a light-weight fabric.',
        'stitch-down' = 'A method of boot construction that helps seal the boot against dirt, mud, and water and maximizes flexibility.',
        'unknown' = 'Unknown term.'
    };
</cfscript>
<cfoutput>#terms[term]#</cfoutput>

The funny thing is, if I move the code at the top of the page below the implicit struct, the page loads fine! Here's some code that should work now:


<cfscript>    
    terms = {
        'oil-tanned' = 'A method of leather tanning in which oils or fats are used to cure the leather. Such leather is usually very supple and has a matte or "oily" finish and is not generally polishable.',
        'full-grain' = 'Leather which has not been altered beyond hair removal. Full-grain leather is the most genuine type of leather, as it retains all of the original texture and markings of the original hide.',
        'vibram' = 'A brand of boot and shoe sole created by Vitale Bramani in the 1930s, orginally for climbing boots. They are easily identified by the distinctive yellow Vibram® octagon. The Vibram® brand is recognized worldwide as the leader in high performance soling products for outdoor, dress casual, and service footwear.',
        'goodyear welt' = 'The Goodyear welt is a method of attaching the sole of a shoe to the upper that is hand-stitched and allows multiple sole replacements, extending the life of the footwear.',
        'cambrelle' = ' A non-woven synthetic fabric used primarily as a lining for shoes, boots and slippers.',
        'cordura' = 'A certified fabric from INVISTA used in a wide range of products from luggage and backpacks to boots, to military wear and performance apparel. Cordura® is resistant to abrasions, tears and scuffs.',
        'gore-tex' = 'A water-proof and breathable fabric that offers superior insulating abilities in a light-weight fabric.',
        'stitch-down' = 'A method of boot construction that helps seal the boot against dirt, mud, and water and maximizes flexibility.',
        'unknown' = 'Unknown term.'
    };

    if ( not IsDefined('term') ) {
        term = 'unknown';
    };
    term = lcase(term);
</cfscript>
<cfoutput>#terms[term]#</cfoutput>

If I'm doing something wrong, please don't hesitate to point it out to me. Thanks!

More Entries

© 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.