Copy link to clipboard
Copied
Long story short:
I have a database table with literally millions of records. Every week a new column is added to this table dynamically.
I need to find out the name of this column is. I tried using the getColumnList() function, and it works fine – in theory. The thing is, I have to use a “select * from” query, which I so do not want to do – I have no desire to select asterisk a couple of million records just to get a single column header value.
I know I’m being an idiot here, but can someone please tell me how to get a column header from a table. CF8, MySql
Thanks!
Copy link to clipboard
Copied
For new versions of ColdFusion check out the cfdbinfo tag. For older versions of ColdFusion, use SELECT * FROM table, but add a WHERE clause so you don't get all records back. For instance, WHERE 1=0.
Copy link to clipboard
Copied
I agree with Jochem's approach but also DB's usually have some mechanism to query the schema. SQL Server has a syscolumns table, or SP_HELP can fetch info about a table. On Oracle one can DESC a table.
--
Adam
Copy link to clipboard
Copied
I also agree with Jochem. I tried it in conjunction with a <cfdump> and <cfabort> and got back just the
Result, which listed all the headings,
the SQL,
the Execution Time,
and the Cached value.
Copy link to clipboard
Copied
The inneficiency of doing a SELECT * lies in the number of columns, not the number of rows. Since you don't plan on running that query very often, that's an ineffeciency that you should be able to live with, and it gives you the data (columnnames) that you need. Just couple it with one of the suggested means of limiting the results set (WHERE 1=2, SELECT TOP 1 *, etc) and you shouldn't have any problems, and it will work no matter what type of data you are querying.
Copy link to clipboard
Copied
If the column is being added by coldfusion, alter that code so you get notified when it happens. Otherwise, if you have permission, query the applicable system table to get your field info.