Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 458
Justin Garrick Avatar asked Apr 06 '12 19:04

Justin Garrick


2 Answers

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+

like image 200
Arion Avatar answered Oct 18 '22 08:10

Arion


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.

like image 27
abatishchev Avatar answered Oct 18 '22 09:10

abatishchev