查找视图中使用的别名的真实列名? [英] Find the real column name of an alias used in a view?

问题描述

假设我有一个视图，其中一些列名是别名，例如本例中的surName":

CREATE VIEW myView AS选择名，中间名字，you.lastName 姓氏从我的表我LEFT OUTER JOIN yourTable youON me.code = 你.code走

我可以使用 INFORMATION_SCHEMA 视图检索有关视图的一些信息.
例如查询

SELECT column_name AS ALIAS, data_type AS TYPEFROM information_schema.columnsWHERE table_name = 'myView'

产量:

<前>----------------|别名 |类型 |----------------|名字 |nchar||中间名|nchar||姓 |nchar|----------------

但是，我也想知道实际的列名.理想情况下:

<前>---------------------------|别名 |类型 |真实姓名 |---------------------------|名字 |nchar|名字 ||中间名|nchar|中间名||姓氏 |nchar|姓氏 |---------------------------

如何根据别名确定真正的列名?必须有某种方法可以使用 sys 表和/或 INFORMATION_SCHEMA 视图来检索此信息.

<小时>

我可以接近这种憎恶，这类似于 Arion 的回答:

SELECTc.name AS ALIAS,ISNULL(type_name(c.system_type_id), t.name) AS DATA_TYPE,tablecols.name AS REALNAME从sys.views vJOIN sys.columns c ON c.object_id = v.object_id左连接 sys.types t ON c.user_type_id = t.user_type_idJOIN sys.sql_dependencies d ON d.object_id = v.object_idAND c.column_id = d.referenced_minor_idJOIN sys.columns tablecols ON d.referenced_major_id = tablecols.object_idAND tablecols.column_id = d.referenced_minor_idAND tablecols.column_id = c.column_idWHERE v.name ='myView'

这产生:

<前>---------------------------|别名 |类型 |真实姓名 |---------------------------|名字 |nchar|名字 ||中间名|nchar|中间名||姓氏 |nchar|代码 ||姓氏 |nchar|姓氏 |---------------------------

但第三条记录是错误的——这发生在使用JOIN"子句创建的任何视图中，因为有两列具有相同的column_id"，但在不同的表中.

解决方案

鉴于此观点:

CREATE VIEW viewTest作为选择书籍编号，书.作者，Books.title AS 名称从图书

我所看到的你可以通过这样做获得使用的列和使用的表:

SELECT *FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumnsWHERE UsedColumns.VIEW_NAME='viewTest'选择 *FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE AS UsedTablesWHERE UsedTables.VIEW_NAME='viewTest'

这适用于 sql server 2005+.请参阅参考此处

编辑

给出相同的观点.试试这个查询:

SELECTc.name AS columnName,columnTypes.name 作为数据类型，aliases.name 作为别名从sys.views v加入 sys.sql_dependencies dON d.object_id = v.object_id加入 .sys.objects tON t.object_id = d.referenced_major_id加入 sys.columns cON c.object_id = d.referenced_major_idJOIN sys.types AS columnTypesON c.user_type_id=columnTypes.user_type_idAND c.column_id = d.referenced_minor_idJOIN sys.columns AS 别名在 c.column_id=aliases.column_idAND aliases.object_id = object_id('viewTest')在哪里v.name = 'viewTest';

它为我返回这个:

columnName 数据类型别名身份证号码作者 varchar 作者标题 varchar 名称

这也在 sql 2005+ 中测试过

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+

这篇关于查找视图中使用的别名的真实列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！