Retrieve column headers for INFORMATION_SCHEMA

Question

Retrieve column headers for INFORMATION_SCHEMA

at work

Old Hand

Points: 371
More actions
February 11, 2009 at 7:21 am

#128717

Hi all,
I've been trying to do this for the past hour and I'm all googled out! Can anybody please point me in the right direction?
SQL SERVER 2005
I want to retrieve the schema for a particular table, say "table1" and display it in a web page and I can do that using:
SELECT * FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_name='table1'
But I also want the column headers of INFORMATION_SCHEMA.COLUMNS too. This is the closest I have got, but it is obviously throwing cast errors. There must be a simpler way without casting, but my schema skills aren't good enough to figure it out:
SELECT
'TABLE_CATALOG',
'TABLE_SCHEMA',
'TABLE_NAME',
'COLUMN_NAME',
'ORDINAL_POSITION',
'COLUMN_DEFAULT',
'IS_NULLABLE',
'DATA_TYPE',
'CHARACTER_MAXIMUM_LENGTH',
'CHARACTER_OCTET_LENGTH',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC',
'ETC'
UNION ALL
SELECT * FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_name='table1'
Any help would be very much appreciated, thanks 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

{edit}ok, what are "column ?Headers...that's what i'm not understanding.

maybe I'm oversimplifying, but since the table_name exists in information_schema.columns, why not just select directly fromt here?

select * from

INFORMATION_SCHEMA.COLUMNS

where table_name ='mytable'

what did i miss on your question?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 2

Ok i think i know what you want;

in SSMS go to tools options.

check the shown checkbox, and when you copy/paste any results, the column names will be included:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

at work Old Hand Points: 371 More actions · Answer 3

Hi Lowell,

Thanks for helping out.

Yes it is the column headers for the INFORMATION_SCHEMA that I am trying to return with the schema information e.g.

TABLE_CATALOG',

'TABLE_SCHEMA',

'TABLE_NAME',

'COLUMN_NAME',

'ORDINAL_POSITION'

etc etc etc

I saw the solution you have offered while I was google'ing, however, the results are being sent to a web page and the site is hosted in a shared environment and I don't have access to the options you are suggesting. I'm sure it can be achieved in a single sql query though, I just can't figure it out!

Any other ideas? 🙂

Lowell SSC Guru Points: 323504 More actions · Answer 4

well, the web page itself would run the query, and stick the results in either an ADODB.Recordset, or a .NET DataTable or DataReader, right?

both of those have access to teh column names automatically... it's built in...

for example, in an asp page using ADODB recordset,

Response.Write " "

Do while not rs.EOF

For widgit = 0 to rs.Fields.Count -1

Response.Write rs(widgit).Name & ": " & rs(widgit).Value & "

"

Next 'widgit

rs.MoveNext

Loop

if it was .NET, it would be DataTable.Columns(0).ColumnName, iterating thru DataTable.Columns.Count -1

is that the question?

at work (2/11/2009)
Hi Lowell,
Thanks for helping out.
Yes it is the column headers for the INFORMATION_SCHEMA that I am trying to return with the schema information e.g.
TABLE_CATALOG',
'TABLE_SCHEMA',
'TABLE_NAME',
'COLUMN_NAME',
'ORDINAL_POSITION'
etc etc etc
I saw the solution you have offered while I was google'ing, however, the results are being sent to a web page and the site is hosted in a shared environment and I don't have access to the options you are suggesting. I'm sure it can be achieved in a single sql query though, I just can't figure it out!
Any other ideas? 🙂

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

Hi

Lowell solution is right solution, so this way you are hard coding the column names any where in the query. This is so dynamic.

Thanks -- Vijaya Kadiyala

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers