select column name and give alias

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

General SQL Server Forums

New to SQL Server Programming

select column name and give alias

Author

Topic

x-jeno
Starting Member

6 Posts

Posted - 2011-07-20 : 15:42:01

Hi
I used the following sql query
Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
and managed to get every column name from my database.

But when I try to use it for a specific table, by adding for example
Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "Member"
it says Invalid column name 'Member'.

May I know what's wrong?

And also I would like to know if it is possible to give the selected columns name alias.

I am using sql2008.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-20 : 15:50:34

Change "Member" to 'Member'

Double quotes to single.

x-jeno
Starting Member

6 Posts

Posted - 2011-07-20 : 15:54:19

Opps.
That was careless of me.
Thank you!

yogi86
Starting Member

13 Posts

Posted - 2011-07-20 : 16:26:15

To give COLUMN_NAME an alias simlpy put

SELECT COLUMN_NAME as 'Greatest Name Ever', COLUMN_NAME2 as 'Second Greatest Name Ever'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MEMBER'

I hope this is what you're looking for.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-07-20 : 16:52:38

quote:
Originally posted by yogi86

To give COLUMN_NAME an alias simlpy put

SELECT COLUMN_NAME as 'Greatest Name Ever', COLUMN_NAME2 as 'Second Greatest Name Ever'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MEMBER'

I hope this is what you're looking for.

Actually, you want to be caseful about using single-quotes to create a Delimited Identifier because you could get unexpected results depending on the setting of the QUOTED_IDENTIFIER option. I, personaly, would go with the square brackets becuase it's more obvious that the intent is a delimited identifier and not someone borking up creating a string.

From BOL:

quote:
The name of a result set column is an identifier. If the name is a regular identifier that follows the rules for identifiers, it does not have to be delimited. If the name does not follow the rules for identifiers it must be delimited using either brackets ([]) or double quotation marks (""). Double quotation marks can be used to delimit result set column names, regardless of the setting of the QUOTED_IDENTIFIER option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources