Find the real column name of an alias used in a view?

Question

Suppose I have a view in which some of the column names are aliases, like "surName" in this example:

CREATE VIEW myView AS
    SELECT  
            firstName,
            middleName,
            you.lastName surName
    FROM 
            myTable me
            LEFT OUTER JOIN yourTable you
            ON me.code = you.code
GO

I'm able to retrieve some information about the view using the INFORMATION_SCHEMA views.
For example, the query

SELECT column_name AS ALIAS, data_type AS TYPE
FROM information_schema.columns 
WHERE table_name = 'myView'

yields:

 ----------------
|ALIAS     |TYPE |
 ----------------
|firstName |nchar|
|middleName|nchar|
|surName   |nchar|
 ----------------

However, I would like to know the actual column name as well. Ideally:

 ---------------------------
|ALIAS     |TYPE |REALNAME  |
 ---------------------------
|firstName |nchar|firstName |
|middleName|nchar|middleName|
|surName   |nchar|lastName  |
 ---------------------------

How can I determine what the real column name is based on the alias? There must be some way to use the sys tables and/or INFORMATION_SCHEMA views to retrieve this information.

---

EDIT: I can get close with this abomination, which is similar to Arion's answer:

SELECT
    c.name AS ALIAS,
    ISNULL(type_name(c.system_type_id), t.name) AS DATA_TYPE,
    tablecols.name AS REALNAME
FROM 
    sys.views v
    JOIN sys.columns c ON c.object_id = v.object_id
    LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
    JOIN sys.sql_dependencies d ON d.object_id = v.object_id 
        AND c.column_id = d.referenced_minor_id
    JOIN sys.columns tablecols ON d.referenced_major_id = tablecols.object_id 
        AND tablecols.column_id = d.referenced_minor_id 
        AND tablecols.column_id = c.column_id
WHERE v.name ='myView'

This yields:

 ---------------------------
|ALIAS     |TYPE |REALNAME  |
 ---------------------------
|firstName |nchar|firstName |
|middleName|nchar|middleName|
|surName   |nchar|code      |
|surName   |nchar|lastName  |
 ---------------------------

but the third record is wrong -- this happens with any view created using a "JOIN" clause, because there are two columns with the same "column_id", but in different tables.

Answer 1

Given this view:

CREATE VIEW viewTest
AS
SELECT
    books.id,
    books.author,
    Books.title AS Name
FROM
    Books

What I can see you can get the columns used and the tables used by doing this:

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns 
WHERE UsedColumns.VIEW_NAME='viewTest'

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE AS UsedTables 
WHERE UsedTables.VIEW_NAME='viewTest'

This is for sql server 2005+. See reference here

Edit

Give the same view. Try this query:

SELECT
    c.name AS columnName,
    columnTypes.name as dataType,
    aliases.name as alias
FROM 
sys.views v 
JOIN sys.sql_dependencies d 
    ON d.object_id = v.object_id
JOIN .sys.objects t 
    ON t.object_id = d.referenced_major_id
JOIN sys.columns c 
    ON c.object_id = d.referenced_major_id 
JOIN sys.types AS columnTypes 
    ON c.user_type_id=columnTypes.user_type_id
    AND c.column_id = d.referenced_minor_id
JOIN sys.columns AS aliases
    on c.column_id=aliases.column_id
    AND aliases.object_id = object_id('viewTest')
WHERE
    v.name = 'viewTest';

It returns this for me:

columnName  dataType  alias

id          int       id
author      varchar   author
title       varchar   Name

This is also tested in sql 2005+

Answer 2

Having spent a number of hours trying to find an answer to this, and repeatedly running into solutions that didn't work and posters that appeared to eventually give up, I eventually stumbled across an answer here that appears to work:

https://social.msdn.microsoft.com/Forums/windowsserver/en-US/afa2ed2b-62de-4a5e-ae70-942e75f887a1/find-out-original-columns-name-when-used-in-a-view-with-alias?forum=transactsql

The following SQL returns, I believe, exactly what you're looking for, it's certainly doing what I need and appears to perform well too.

SELECT  name
    , source_database
    , source_schema
    , source_table
    , source_column
    , system_type_name
    , is_identity_column
FROM    sys.dm_exec_describe_first_result_set (N'SELECT * from ViewName', null, 1) 

Documentation on the sys.dm_exec_describe_first_result_set function can be found here, it's available in SQL Server 2012 and later:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql

Full credit to the poster on the link, I didn't work this out myself, but I wanted to post this here in case it's useful to anyone else searching for this information as I found this thread much more easily than the one I linked to.

Answer 3

I think you can't.

Select query hides actual data source it was performed against. Because you can query anything, i.e. view, table, even linked remote server.

Answer 4

Not a Perfect solution; but, it is possible to parse the view_definition with a high degree of accuracy especially if the code is well organized with consistent aliasing by 'as'. Additionally, one can parse for a comma ',' after the alias.

Of note: the final field in the select clause will not have the comma and I was unable to exclude items being used as comments (for example interlaced in the view text with --)

I wrote the below for a table named 'My_Table' and view correspondingly called 'vMy_Table'

select alias, t.COLUMN_name
from 
(
select VC.COLUMN_NAME, 



case when
ROW_NUMBER () OVER (
partition by C.COLUMN_NAME order by 
CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))- 
CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION)

) = 1

then 1
else 0 end
as lenDiff



,C.COLUMN_NAME as alias

 ,CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION)) diff1
 , CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) diff2

 from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE VC
inner join INFORMATION_SCHEMA.VIEWS V on V.TABLE_NAME = 'v'+VC.TABLE_Name
inner join information_schema.COLUMNS C on C.TABLE_NAME = 'v'+VC.TABLE_Name
where VC.TABLE_NAME = 'My_Table'
and CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))- 
CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) >0
)
t

where lenDiff = 1 

Hope this helps and I look forward to your feedback