get a list of all field names

[revdave]

I forgot the sql statement - how do I get a list of all field names in a table?

[revdave]

BTW: I need it for ms ACCESS & MS sequel server...

i think i got it working with

queryname.ColumnList


q: but how do I get it to sort from the order it is in the database?

[Dan_Bracuk]

Don't use select *. Invariably you'll bring back more data than you need. Another approach is:

fields = "f1,f2,etc";

select #fields# from yourtable

<cfoutput query="yourquery">
<cfloop list = "#fields#" index = "ii">
#yourquery[ii]#

[revdave]

quote:

---

Originally posted by: Dan Bracuk
Don't use select *. Invariably you'll bring back more data than you need. Another approach is:

fields = "f1,f2,etc";

select #fields# from yourtable

<cfoutput query="yourquery">
<cfloop list = "#fields#" index = "ii">
#yourquery[ii]#

---

Hi Dan - thanks much for you help.

I'm a bit confused - i don't have the fields yet - that's what I'm trying to get - so I don't know how to use:
yours .....fields = "f1,f2,etc";

anyway

- actually I said:

<cfquery ...........
select * from mytable where id = 1
</query ............

then

<cfoutput .....
#queryname.ColumnList#
</cfoutput........

which DOES work - BUT i need it to output in *ORIGINAL* order from the database (MS ACCESS) - (currently it is not that)


thanks again

[Newsgroup_User]

I think what you want to do is query the system tables that describe all
the other tables of the database.

That is the convient thing about database management systems, they use
themselves to describe themselves. So all the definitions of fields and
tables are simply records in other tables. Once you know those tables
it is simple SQL queries to get the information you seek.

But each database management system uses different tables to do this,
but a simple google search should turn up the specifics you need for any
desired database management system.

If you are not the DBA of these databases though, you may not be using
an database user that has permission to query these system tables. And
hopefully the database user you use for the database functionality of
your website is *NOT* such a user.

[revdave]

Thanks Skinner - i just found this thread - but i have no idea how to get the table's field names from it...

- - - - -
From: Access: View system tables in Access 2003/XP/2000/97
Location: http://www.techonthenet.com/access/database/view_systables.php
- - - - -

Info:
Access: View system tables in Access 2003/XP/2000/97

- any ideas?

[Newsgroup_User]

> I think what you want to do is query the system tables that describe all
> the other tables of the database.

The OP did say they're using Access. I'd say all bets are off in regards
to querying "system tables" in Access, don't you think?

--
Adam

[Newsgroup_User]

> database (MS ACCESS)

Why? Why are you using MS Access? MS Access is a desktop application
designed for storing recipes and CD collections and... err... I dunno...
lists of "favourite My Little Ponies" and pointless sh!t like that. It
serves no practical application beyond that.

It's certainly not suitable for providing a back-end for a server
application, irrespective of how small the app is.

If you're the decison maker as to which DB to use here... stop using Access
before you go any further. Get hold of SQL Server Express or MySQL or
something like that.

--
Adam

[Newsgroup_User]

> I'm a bit confused - i don't have the fields yet - that's what I'm trying to
> get - so I don't know how to use:

How is it you don't know the column names of the table you're querying?
It's all well and good to query it with "SELECT *" (well: that's very poor
practice in itself, but hey), but at some point you're going to actually
need to refer to the columns by name. What are you trying to do with this
data, having pulled it from the DB?

--
Adam

[revdave]

I just saw this - is it STILL true (*sorted alphabetically*;)?
Is there any way to get the list in ORIGINAL order?


- - - - -
From: ColdFusion MX 7 -- ColdFusion tag-specific variables -- Version 7
Location: http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFu...
- - - - -

Info:
Oblio said on Mar 21, 2006 at 8:59 AM :
It's really critical to note that query.ColumnList is *sorted alphabetically*; it does not display a list of columns in the order that they were returned from the database. This is also true for queries created by the QueryNew function.