SQL select column 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)

SQL select column names

Author

Topic

kerri
Starting Member

1 Post

Posted - 2003-10-03 : 14:00:16

Is there a way to select just column names out of a database? I want to be able to print the column names to the screen.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 14:10:49

The INFORMATION_SCHEMA views can do this for you. Just change SomeTable to the name of your table. Also look up the INFORMATION_SCHEMA views in Books Online so that you know what their purpose is and what you can get from them.

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SomeTable'

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 14:12:51

OK...but are you sure you don't want to know what table the column came from?


  SELECT COLUMN_NAME 
    FROM INFORMATION_SCHEMA.Columns 
ORDER BY COLUMN_NAME

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 14:13:48

Damn beat me by 2 minutes...

Ahhh Youth...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 14:23:22

Actually, the query should be:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SomeTable'
ORDER BY ORDINAL_POSITION

I added the ORDERY BY ORDINAL_POSITION so that it displays the column names in the order that they appear in the table. These views are stored in the master database BTW.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 14:26:26

quote:
Originally posted by tduggan

Actually, the query should be:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SomeTable'
ORDER BY ORDINAL_POSITION

I added the ORDERY BY ORDINAL_POSITION so that it displays the column names in the order that they appear in the table. These views are stored in the master database BTW.

Tara

Well that depends...would you want to do a lookup of a column bu ordinal position or by alphabetic?

And s/he didn't mention that it was for 1 table or all tables...which goes back to my original question...and if it is for all...

SELECT DISTINCT COLUMN_NAME....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-03 : 14:31:48

If I was displaying columns, I would want them by ordinal position but I guess it depends on what s/he is trying to achieve. Maybe s/he is just trying to see the information like what would be displayed on an ERD.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources