SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Get Column Names in View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zosonc
Starting Member

4 Posts

Posted - 01/06/2009 :  14:05:21  Show Profile  Reply with Quote
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.

Edited by - zosonc on 01/06/2009 14:58:43

Skorch
Constraint Violating Yak Guru

USA
300 Posts

Posted - 01/06/2009 :  14:58:52  Show Profile  Reply with Quote
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.
Go to Top of Page

zosonc
Starting Member

4 Posts

Posted - 01/06/2009 :  15:23:09  Show Profile  Reply with Quote
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?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 01/06/2009 :  17:01:28  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

zosonc
Starting Member

4 Posts

Posted - 01/06/2009 :  17:10:55  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 01/06/2009 :  17:11:56  Show Profile  Visit webfred's Homepage  Reply with Quote
no chance


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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/06/2009 :  19:33:39  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/07/2009 :  00:24:56  Show Profile  Reply with Quote
You need to view definition and find with search.
Go to Top of Page

mkokabi
Starting Member

1 Posts

Posted - 02/02/2009 :  19:02:57  Show Profile  Reply with Quote
You can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
Go to Top of Page

rhysmeister
Starting Member

6 Posts

Posted - 02/03/2009 :  09:29:13  Show Profile  Visit rhysmeister's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/03/2009 :  09:46:35  Show Profile  Reply with Quote
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
Go to Top of Page

chevrollbryan
Starting Member

USA
1 Posts

Posted - 01/12/2010 :  13:48:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000