Найдите реальное имя столбца псевдонима, используемого в представлении?

Предположим, что у меня есть представление, в котором некоторые имена столбцов являются псевдонимами, такими как "surName" в этом примере:

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

Я могу получить некоторую информацию о представлении, используя представления INFORMATION_SCHEMA.
Например, запрос

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

дает:

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

Однако мне также хотелось бы знать имя столбца. В идеале:

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

Как определить, какое имя реального столбца основано на псевдониме?. Чтобы получить эту информацию, должен быть какой-то способ использовать таблицы sys и/или INFORMATION_SCHEMA.

EDIT: Я могу приблизиться к этой мерзости, которая похожа на ответ Ариона:

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'

Это дает:

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

но третья запись ошибочна - это происходит с любым представлением, созданным с помощью предложения JOIN, потому что есть два столбца с одинаковым "column_id", но в разных таблицах.

Ответы

Ответ 1

Учитывая это представление:

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

Что я вижу, вы можете получить используемые столбцы и таблицы, используемые при этом:

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'

Это для SQL Server 2005+. См. Ссылку здесь

Edit

Дайте тот же вид. Попробуйте этот запрос:

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';

Он возвращает это для меня:

columnName  dataType  alias

id          int       id
author      varchar   author
title       varchar   Name

Это также проверено в sql 2005 +

Ответ 2

Я думаю, вы не можете.

Выбор запроса скрывает фактический источник данных, с которым он был выполнен. Потому что вы можете запросить что угодно, т.е. Просматривать, таблицы, даже связанные удаленные серверы.

Ответ 3

Не идеальное решение; но можно с высокой степенью точности проанализировать view_definition, особенно если код хорошо организован с последовательным псевдонимом с помощью "as". Кроме того, после псевдонима можно проанализировать запятую ','.

Примечание: в последнем поле в предложении select не будет запятой, и я не смог исключить элементы, используемые в качестве комментариев (например, чередующиеся в тексте вида с -)

Я написал ниже для таблицы с именем "My_Table" и просмотрел соответственно "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

Надеюсь, что это поможет, и я с нетерпением жду ваших отзывов.