Query to extract field names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to extract field names

Author

Topic

marvik
Starting Member

7 Posts

Posted - 2006-11-10 : 04:51:03

Hi,
How to wright a query to extract field names in table (SQL Server 2000)?
Thanx in adv.
cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 04:52:12

you mean column name ?

use INFORMATION_SCHEMA.COLUMNS

marvik
Starting Member

7 Posts

Posted - 2006-11-10 : 04:54:33

Yes,All column names. Few possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 05:00:27

"Few possible?"
What do you mean ?

marvik
Starting Member

7 Posts

Posted - 2006-11-10 : 05:03:28

quote:
Originally posted by khtan

"Few possible?"
What do you mean ?

KH

like first 5 or 10 cloumns.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 05:29:29

[code]
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'yourtable' and ORDINAL_POSITION <= 5
[/code]

marvik
Starting Member

7 Posts

Posted - 2006-11-10 : 05:32:30

quote:

Originally posted by khtan


select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'yourtable' and ORDINAL_POSITION <= 5

Select COLUMN_NAME
From Information_Schema.Columns
Where Table_Name = 'student'

This query displays empty. Any mistake in this Query?
Thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:40:20

Information_schema views include only the column in current database.
Do a "select * from information_schema.columns" to see what you get.

Peter Larsson
Helsingborg, Sweden

marvik
Starting Member

7 Posts

Posted - 2006-11-10 : 05:45:59

quote:
Originally posted by Peso

Information_schema views include only the column in current database.
Do a "select * from information_schema.columns" to see what you get.

Peter Larsson
Helsingborg, Sweden

Hi,
It displays TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME etc.
But actally i need column names in a table. My table contains 10 fields or columns. I want to extarct.
Cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 06:01:38

Make sure you are firing query in the database where your student table resides. Also make sure table name is correctly written.

Also try these other solutions:

1.

sp_columns 'student'

sp_help 'student'

select name from syscolumns where object_name(id) = 'Student'

Harsh Athalye
India.
"Nothing is Impossible"

marvik
Starting Member

7 Posts

Posted - 2006-11-10 : 06:13:13

Thankyou guys, worked fine, cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-10 : 11:06:08

quote:
Originally posted by marvik

quote:
Originally posted by khtan

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'yourtable' and ORDINAL_POSITION <= 5

KH

Select COLUMN_NAME
From Information_Schema.Columns
Where Table_Name = 'student'

This query displays empty. Any mistake in this Query?
Thanx

It means you are looking at different database.

Try

Select COLUMN_NAME
From DBName.Information_Schema.Columns
Where Table_Name = 'student'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources