How do I return the column name 1

[SelbyGlenn]

Hi there,

How do I query SQL to bring back the column names in a table?

Thanks in advance,



Glenn
BEng MCSE CCA

 

[JamesLean]

This will depend on which DBMS you are using - suggest you post in a forum dedicated to that particular system.

--James

 

[JarlH]

The ANSI answer is:

SELECT COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'SELBYGLENN'
AND TABLE_NAME = 'GLENNTABLE'


If you want more column information, e.g. data type, replace COLUMN_NAME with * in the above statement.

 

[SelbyGlenn]

Hi JarlH,

I've managed to return data using:

Select COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'GLENNTABLE'

However, the data it has returned does not correspond to the names of the fields in GLENNTABLE. There seems to additional data returned.

When I try adding the WHERE TABLE_SCHEMA = 'SELBYGLENN' command, no data is returned. Can you please explain what TABLE_SCHEMA is?

Thanks again for all your help.

Glenn
BEng MCSE CCA

 

[SelbyGlenn]

Ok, I've looked a little closer and it is bringing back all the right data (I didn't realise I had so many columns in the table!) However, it has brought them back in a different order. How is this possible?

Thanks again.

Glenn
BEng MCSE CCA

 

[swampBoogie]

Select COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'GLENNTABLE'
order by ordinal_position

 

[SelbyGlenn]

Lovely Jubley!
That did the trick!
Thanks for the quick responce SwampBoogie. Have a STAR!

Glenn
BEng MCSE CCA