poner - nombre de columna dinamico sql

Encontrar el nombre de columna real de un alias utilizado en una vista?

Question

Supongamos que tengo una vista en la que algunos de los nombres de columna son alias, como "surName" en este ejemplo:

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

Puedo recuperar cierta información sobre la vista utilizando las vistas INFORMATION_SCHEMA.
Por ejemplo, la consulta

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

rendimientos:

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

Sin embargo, me gustaría saber el nombre de la columna real también. Idealmente:

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

¿Cómo puedo determinar cuál es el verdadero nombre de columna basado en el alias? Debe haber alguna forma de utilizar las tablas sys y / o las vistas INFORMATION_SCHEMA para recuperar esta información.

EDITAR: Puedo acercarme a esta abominación, que es similar a la respuesta de Arion:

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

Esto produce:

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

pero el tercer registro es incorrecto: esto sucede con cualquier vista creada usando una cláusula "JOIN", porque hay dos columnas con el mismo "column_id", pero en tablas diferentes.

Answer 1

Creo que no puedes.

La consulta seleccionada oculta el origen de datos real con el que se realizó. Debido a que puede consultar cualquier cosa, es decir, ver, tabla, incluso servidor remoto vinculado.

Answer 2

Dado este punto de vista:

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

Lo que puedo ver puede obtener las columnas utilizadas y las tablas utilizadas al hacer esto:

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

Esto es para el servidor SQL 2005+. Ver referencia aquí

Editar

Da la misma vista. Prueba esta consulta:

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

Me devuelve esto:

columnName  dataType  alias

id          int       id
author      varchar   author
title       varchar   Name

Esto también se prueba en sql 2005+

Answer 3

No es una solución perfecta; pero, es posible analizar la definición de vista con un alto grado de precisión, especialmente si el código está bien organizado con aliasing consistente por ''como''. Además, uno puede analizar una coma '','' después del alias.

Nota: el campo final en la cláusula de selección no tendrá la coma y no pude excluir los elementos que se utilizan como comentarios (por ejemplo, entrelazado en el texto de la vista con -)

Escribí lo siguiente para una tabla llamada ''Mi_Tabla'' y veo correspondientemente llamada ''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 

Espero que esto ayude y espero sus comentarios