| Author |
 Topic  |
|
|
zosonc
Starting Member
4 Posts |
 Posted - 01/06/2009 : 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. |
Edited by - zosonc on 01/06/2009 14:58:43
|
|
|
Skorch
Posting Yak Master
USA
237 Posts |
Posted - 01/06/2009 : 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 - 01/06/2009 : 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
Flowing Fount of Yak Knowledge
Germany
5173 Posts |
Posted - 01/06/2009 : 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 - 01/06/2009 : 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
Flowing Fount of Yak Knowledge
Germany
5173 Posts |
Posted - 01/06/2009 : 17:11:56
|
no chance
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sunitabeck
Posting Yak Master
USA
215 Posts |
Posted - 01/06/2009 : 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
Flowing Fount of Yak Knowledge
USA
6718 Posts |
Posted - 01/07/2009 : 00:24:56
|
| You need to view definition and find with search. |
|
|
|
mkokabi
Starting Member
1 Posts |
Posted - 02/02/2009 : 19:02:57
|
| You can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE |
|
|
|
rhysmeister
Starting Member
6 Posts |
Posted - 02/03/2009 : 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
India
27067 Posts |
|
|
chevrollbryan
Starting Member
USA
1 Posts |
Posted - 01/12/2010 : 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 |
|
|
| |
Topic |
|
Get Column Names in View
