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