Get Column Names in View - 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 2005 Forums

Transact-SQL (2005)

Get Column Names in View

Author

Topic

zosonc
Starting Member

4 Posts

Posted - 2009-01-06 : 14:05:21

I have a view that has some colums that use aliases. I need a way to get the table colum name for the view colums with the alia.

For example. With this view:

CREATE VIEW [dbo].[vTest]
AS
SELECT CustomerID, ContactName, ContactTitle, Address, City, CompanyName AS C2
FROM dbo.Customers

I want to get that the "C2" colimn in the view is actually the CompanyName column.

I don't care if it is a function, SP or T-SQL. (I'll take whatever I can get)

Thanks in advance.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-06 : 14:58:52

So you're trying to remove the alias on the columns? You should be able to just alter the view and remove the alias that way.

Find the view in your Object Explorer, right click and choose Script View as ALTER VIEW to New Query Editor Window. Then remove the aliases on the columns and run the alter script.

zosonc
Starting Member

4 Posts

Posted - 2009-01-06 : 15:23:09

Thanks Skorch but I'm not trying to remove the alias. I just need to get the column name itself that the alaias uses in orde to gove that to another process to use. Make sense?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-06 : 17:01:28

quote:
Find the view in your Object Explorer, right click and choose Script View as ALTER VIEW to New Query Editor Window

Then do it like Skorch suggested and do nothing else but looking in the select list of the view-source-code.
There you can see the real column name.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

zosonc
Starting Member

4 Posts

Posted - 2009-01-06 : 17:10:55

I am looking for a way to do it within a SQL stmt. I will need to get it in an application not SQL Studio

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-06 : 17:11:56

no chance

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-06 : 19:33:39

I can't see this being easily done from a T-SQL query or SMO or another programmatic feature. For example, if it were possible, what would be returned as the "real column name" for a view such as this:

create view myTestView as select t1.col1 + t1.col2 as viewCol1 from t1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-07 : 00:24:56

You need to view definition and find with search.

mkokabi
Starting Member

1 Post

Posted - 2009-02-02 : 19:02:57

You can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

rhysmeister
Starting Member

6 Posts

Posted - 2009-02-03 : 09:29:13

Will return the alias

SELECT v1.name
FROM sys.columns v1
INNER JOIN sys.views v2 on V1.OBJECT_ID = V2.OBJECT_ID
and V2.NAME = 'VIEWNAME'

No easy way of getting the column and alias name. Probably best off parsing the CREATE VIEW

Twitter Integration | Ping.fm Integration | SQL Server Blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 09:46:35

yup..i also think you need to search with view definition. IF SQL 2005, use sys.sql_modules for searching view definition

http://msdn.microsoft.com/en-us/library/ms175081.aspx

chevrollbryan
Starting Member

1 Post

Posted - 2010-01-12 : 13:48:12

Here is a simple way I have always used to get column names for any tables and or views in your MS SQL Database. It even has enough information to supply you with Data Types and Character Length for the corresponding Columns.

SELECT column_name FROM information_schema.columns
WHERE table_name = 'TABLENAME'

I hope this helps!

...
Chev

...
Chev

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources