Retrieve column headers for INFORMATION

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Retrieve column headers for...

Retrieve column headers for INFORMATION_SCHEMA

Rate Topic

Display Mode

Topic Options

Author

Message

at work

Posted Wednesday, February 11, 2009 7:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 04, 2009 5:22 PM
Points: 9, Visits: 20

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 :)

Post #654804

Lowell

Posted Wednesday, February 11, 2009 7:34 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645

{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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #654815

Lowell

Posted Wednesday, February 11, 2009 7:40 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #654823

at work

Posted Wednesday, February 11, 2009 7:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 04, 2009 5:22 PM
Points: 9, Visits: 20

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? :)

Post #654831

Lowell

Posted Wednesday, February 11, 2009 9:14 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645

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)

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #654970

Vijaya Kadiyala

Posted Wednesday, February 11, 2009 9:47 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, Visits: 409

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

Post #655018

« Prev Topic | Next Topic »

Permissions