How to get column names using SQL?

[Gert van der Kooij]

I am writing a C++ application that not only wants to look at data in
the database, but also the corresponding column names. Is there a SQL
statement to get column names for a certain table? I know I can use
select tabnames from syscat.tables
to get table names, so I assume there should be a similar statement
for getting the column names.
I'm using DB2 UDB V8.1.
Thanks in advance for any help.

SYSCAT.COLUMNS

To show the columns of this view:

'db2 describe table syscat.columns' from the db2 command window

[Blair Adamache]

select colname from syscat.columns where tabname='ADDRESS'

(to get the right schema, you may have to join on syscat.tables.tabname
and syscat.tables.tabschema)

or

describe table adamache.address

I am writing a C++ application that not only wants to look at data in
the database, but also the corresponding column names. Is there a SQL
statement to get column names for a certain table? I know I can use
select tabnames from syscat.tables
to get table names, so I assume there should be a similar statement
for getting the column names.
I'm using DB2 UDB V8.1.
Thanks in advance for any help.

[Knut Stolze]

I am writing a C++ application that not only wants to look at data in
the database, but also the corresponding column names. Is there a SQL
statement to get column names for a certain table? I know I can use
select tabnames from syscat.tables
to get table names, so I assume there should be a similar statement
for getting the column names.
I'm using DB2 UDB V8.1.

Not sure what you really want, so here are three choices:

(1) Query the DB2 catalog to see which tables and columns you have:

SELECT colname
FROM syscat.tables
WHERE tabschema = ... AND tabname = ...

(substitute ... with the appropriate values)

(2) Get the names of the columns returned by a query. That is by no means
the same as the column names in persistent tables!

The best is to do a DESCRIBE:

DESCRIBE SELECT x, y, z FROM table JOIN anotherTable ON ...

(3) Similar as in (2), you can just use explicit column names in your query:

SELECT a AS col1, a+b AS col2, b AS col3
FROM table

[Ian D. Bjorhovde]

Post by Knut Stolze

I am writing a C++ application that not only wants to look at data in
the database, but also the corresponding column names. Is there a SQL
statement to get column names for a certain table? I know I can use
select tabnames from syscat.tables
to get table names, so I assume there should be a similar statement
for getting the column names.
I'm using DB2 UDB V8.1.

SELECT colname
FROM syscat.tables
WHERE tabschema = ... AND tabname = ...
(substitute ... with the appropriate values)

If you're writing your application using the CLI or ODBC, then there
are functions to handle this for you. SQLColumns() does this without
having to write a query.

Also, there is a CLI function SQLDescribeCol() that you can use with an
open statement handle to get the names of the columns for the statement.
(i.e. if you prepare a query "select * from a_table", you can use
SQLNumResultCols() and SQLDescribeCol() to find out the columns in the
result set).

Check the CLI Guide and Reference for more information.


Good luck,





-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

[Knut Stolze]

Post by Knut Stolze
SELECT colname
FROM syscat.tables

Oops, should have been syscat.columns here.

[Mark A]

I am writing a C++ application that not only wants to look at data in
the database, but also the corresponding column names. Is there a SQL
statement to get column names for a certain table? I know I can use
select tabnames from syscat.tables
to get table names, so I assume there should be a similar statement
for getting the column names.
I'm using DB2 UDB V8.1.
Thanks in advance for any help.

Others have given the SQL to get the columns names, but I would add an order
by in the SQL query just to make sure you get them in the right order.

The DB2 Catalog tables are described in the appendix of the SQL Reference
Manual, Volume 1. DB2 manuals in PDF format can be downloaded for free from
the IBM web site.

[minjie]

Post by Mark A
Others have given the SQL to get the columns names, but I would add an order
by in the SQL query just to make sure you get them in the right order.
The DB2 Catalog tables are described in the appendix of the SQL Reference
Manual, Volume 1. DB2 manuals in PDF format can be downloaded for free from
the IBM web site.

Thank you all for the replies. I have one question regarding to 'order
by' as suggested by Mark. The app will display column names and their
values side by side, which means the order have to be exactly the
same. But I will have two SELECT statements, one to get the column
names, and the other to get their values. How can I make sure the
order will be the same for the two SELECTs? For example, the column
names are:
inst_num, creation_date, last_modified_date, A1, A2, B1, B2, C1, C2,
A3, A4, D1, D2, ...
If I use the following statement:
select colname from syscat.columns where tabname='ops' order by
colname
the column names will be selected in the following order:
A1, A2, A3, A4, B1, B2, C1, C2, creation_date, D1, D2, inst_num,
last_modified_date ...
But the values for those columns will still be corresponding to the
order shown in the first case, if I use the following for the 2nd
SELECT:
select * from ops where inst_num = 1;
the 1st value will be 1, instead of A1, the 2nd value will be
2003-07-09-17.36.53.319908, instead of A2, and so on. How should I
order these two SELECTs in this case? I assume there is no way to
order the 2nd SELECT, which is to get a row of data? I know one way to
resolve it is to order the column names now, when I'm still creating
the database. But if later I need to add columns to that table, the
column names will not be in order again. Another way is not to order
the 1st SELECT, but will I be guaranteed that the column names will be
selected in the order when they were created? It seems so after a few
tests, but I'm not sure if it will always work. If I use SQLDA in the
app for the retrieved data, I cannot order them in the code, either,
or can I?
Thanks,
Minjie

[Knut Stolze]

Post by minjie
Thank you all for the replies. I have one question regarding to 'order
by' as suggested by Mark. The app will display column names and their
values side by side, which means the order have to be exactly the
same. But I will have two SELECT statements, one to get the column
names, and the other to get their values. How can I make sure the
order will be the same for the two SELECTs? For example, the column
inst_num, creation_date, last_modified_date, A1, A2, B1, B2, C1, C2,
A3, A4, D1, D2, ...
select colname from syscat.columns where tabname='ops' order by
colname
A1, A2, A3, A4, B1, B2, C1, C2, creation_date, D1, D2, inst_num,
last_modified_date ...
But the values for those columns will still be corresponding to the
order shown in the first case, if I use the following for the 2nd
select * from ops where inst_num = 1;

How about building an explicit select-list and, thus, avoiding the problem
alltogether?

select A1, A2, A3, A4, B1, B2, C1, C2, creation_date, D1, D2, inst_num,
last_modified_date
from ops where inst_num = 1;


If you don't want to do that, change the first select statement like this:

SELECT colname
FROM syscat.columns
WHERE tabname='ops' AND tabschema = ...
ORDER BY colno

Please note that the "colno" contains the numerical values for the column
position. That's the same that "select *" will use.

I also recommend that you add the schema name into your query to make sure
you get the information only for the one table you are interested in.

Post by minjie
Another way is not to order
the 1st SELECT, but will I be guaranteed that the column names will be
selected in the order when they were created? It seems so after a few
tests, but I'm not sure if it will always work.

No, it's not guaranteed. The only safe way to get a consistent ordering is
to use an ORDER BY clause. Everything else is just coincidence.

Post by minjie
If I use SQLDA in the
app for the retrieved data, I cannot order them in the code, either,
or can I?

You can access the column name information once you do the DESCRIBE (see the
SQLNAME attribute in the SQLVAR entries). With that information, you can
sort out which output parameter of the SELECT belongs to which column name.
Given that, you don't need the first query at all. That should also avoid
problems if the query contains some calculations or renamed columns or so,
which just doesn't correspond 1:1 to the columns in the table that you
query.