Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieve column headers for INFORMATION_SCHEMA Expand / Collapse
Author
Message
Posted Wednesday, February 11, 2009 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 4, 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
Posted Wednesday, February 11, 2009 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 12,903, Visits: 32,146

{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
Posted Wednesday, February 11, 2009 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 12,903, Visits: 32,146
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
Posted Wednesday, February 11, 2009 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 4, 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
Posted Wednesday, February 11, 2009 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 12,903, Visits: 32,146
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

--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
Posted Wednesday, February 11, 2009 9:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 »

Add to briefcase

Permissions Expand / Collapse