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.
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+
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With