sql - ver - Cómo obtener dependencias a nivel de columna en una vista
ver dependencias de una columna sql server (5)
Desafortunadamente, SQL Server no almacena explícitamente la asignación entre las columnas de la tabla de origen y las columnas de vista. Sospecho que la razón principal se debe simplemente a la posible complejidad de las vistas (columnas de expresión, funciones llamadas en esas columnas, consultas anidadas, etc.).
La única forma en que puedo pensar para determinar la asignación entre columnas de vista y columnas de origen sería analizar la consulta asociada a la vista o analizar el plan de ejecución de la vista.
El enfoque que describí aquí se centra en la segunda opción y se basa en el hecho de que SQL Server evitará la generación de listas de resultados para las columnas que no requiere una consulta.
El primer paso es obtener la lista de tablas dependientes y sus columnas asociadas requeridas para la vista. Esto se puede lograr a través de las tablas del sistema estándar en SQL Server.
A continuación, enumeramos todas las columnas de la vista a través de un cursor.
Para cada columna de vista, creamos un procedimiento almacenado de envoltorio temporal que solo selecciona la única columna en cuestión de la vista. Debido a que solo se solicita una sola columna, SQL Server solo recuperará la información necesaria para generar esa columna de vista única.
El procedimiento recién creado ejecutará la consulta en modo de solo formato y, por lo tanto, no causará ninguna operación de E / S real en la base de datos, pero generará un plan de ejecución estimado cuando se ejecute. Una vez que se genera el plan de consulta, consultamos las listas de salida del plan de ejecución. Como sabemos qué columna de vista se seleccionó, ahora podemos asociar la lista de salida para ver la columna en cuestión. Podemos refinar aún más la asociación al solo asociar columnas que forman parte de nuestra lista de dependencia original, esto eliminará los resultados de expresión del conjunto de resultados.
Tenga en cuenta que con este método, si la vista necesita unir diferentes tablas para generar la salida, se devolverán todas las columnas necesarias para generar la salida, incluso si no se usa directamente en la expresión de la columna, ya que todavía se requiere directamente.
El siguiente procedimiento almacenado demuestra el método de implementación anterior:
CREATE PROCEDURE ViewGetColumnDependencies
(
@viewName NVARCHAR(50)
)
AS
BEGIN
CREATE TABLE #_suppress_output
(
result NVARCHAR(500) NULL
);
DECLARE @viewTableColumnMapping TABLE
(
[ViewName] NVARCHAR(50),
[SourceObject] NVARCHAR(50),
[SourceObjectColumnName] NVARCHAR(50),
[ViewAliasColumn] NVARCHAR(50)
)
-- Get list of dependent tables and their associated columns required for the view.
INSERT INTO @viewTableColumnMapping
(
[ViewName]
,[SourceObject]
,[SourceObjectColumnName]
)
SELECT v.[name] AS [ViewName]
,''['' + OBJECT_NAME(d.referenced_major_id) + '']'' AS [SourceObject]
,c.[name] AS [SourceObjectColumnName]
FROM sys.views v
LEFT OUTER JOIN sys.sql_dependencies d ON d.object_id = v.object_id
LEFT OUTER JOIN sys.columns c ON c.object_id = d.referenced_major_id AND c.column_id = d.referenced_minor_id
WHERE v.[name] = @viewName;
DECLARE @aliasColumn NVARCHAR(50);
-- Next, we enumerate all of the views columns via a cursor.
DECLARE ViewColumnNameCursor CURSOR FOR
SELECT aliases.name AS [AliasName]
FROM sys.views v
LEFT OUTER JOIN sys.columns AS aliases on v.object_id = aliases.object_id -- c.column_id=aliases.column_id AND aliases.object_id = object_id(''vTEST'')
WHERE v.name = @viewName;
OPEN ViewColumnNameCursor
FETCH NEXT FROM ViewColumnNameCursor
INTO @aliasColumn
DECLARE @tql_create_proc NVARCHAR(MAX);
DECLARE @queryPlan XML;
WHILE @@FETCH_STATUS = 0
BEGIN
/*
For each view column, we create a temporary wrapper stored procedure that
only selects the single column in question from view. The stored procedure
will run the query in format only mode and will therefore not cause any
actual I/O operations on the database, but it will generate an estimated
execution plan when executed.
*/
SET @tql_create_proc = ''CREATE PROCEDURE ___WrapView
AS
SET FMTONLY ON;
SELECT CONVERT(NVARCHAR(MAX), ['' + @aliasColumn + '']) FROM ['' + @viewName + ''];
SET FMTONLY OFF;'';
EXEC (@tql_create_proc);
-- Execute the procedure to generate a query plan. The insert into the temp table is only done to
-- suppress the empty result set from being displayed as part of the output.
INSERT INTO #_suppress_output
EXEC ___WrapView;
-- Get the query plan for the wrapper procedure that was just executed.
SELECT @queryPlan = [qp].[query_plan]
FROM [sys].[dm_exec_procedure_stats] AS [ps]
JOIN [sys].[dm_exec_query_stats] AS [qs] ON [ps].[plan_handle] = [qs].[plan_handle]
CROSS APPLY [sys].[dm_exec_query_plan]([qs].[plan_handle]) AS [qp]
WHERE [ps].[database_id] = DB_ID() AND OBJECT_NAME([ps].[object_id], [ps].[database_id]) = ''___WrapView''
-- Drop the wrapper view
DROP PROCEDURE ___WrapView
/*
After the query plan is generate, we query the output lists from the execution plan.
Since we know which view column was selected we can now associate the output list to
view column in question. We can further refine the association by only associating
columns that form part of our original dependency list, this will eliminate expression
outputs from the result set.
*/
;WITH QueryPlanOutputList AS
(
SELECT T.X.value(''local-name(.)'', ''NVARCHAR(max)'') as Structure,
T.X.value(''./@Table[1]'', ''NVARCHAR(50)'') as [SourceTable],
T.X.value(''./@Column[1]'', ''NVARCHAR(50)'') as [SourceColumnName],
T.X.query(''*'') as SubNodes
FROM @queryPlan.nodes(''*'') as T(X)
UNION ALL
SELECT QueryPlanOutputList.structure + N''/'' + T.X.value(''local-name(.)'', ''nvarchar(max)''),
T.X.value(''./@Table[1]'', ''NVARCHAR(50)'') as [SourceTable],
T.X.value(''./@Column[1]'', ''NVARCHAR(50)'') as [SourceColumnName],
T.X.query(''*'')
FROM QueryPlanOutputList
CROSS APPLY QueryPlanOutputList.SubNodes.nodes(''*'') as T(X)
)
UPDATE @viewTableColumnMapping
SET ViewAliasColumn = @aliasColumn
FROM @viewTableColumnMapping CM
INNER JOIN
(
SELECT DISTINCT QueryPlanOutputList.Structure
,QueryPlanOutputList.[SourceTable]
,QueryPlanOutputList.[SourceColumnName]
FROM QueryPlanOutputList
WHERE QueryPlanOutputList.Structure like ''%/OutputList/ColumnReference''
) SourceColumns ON CM.[SourceObject] = SourceColumns.[SourceTable] AND CM.SourceObjectColumnName = SourceColumns.SourceColumnName
FETCH NEXT FROM ViewColumnNameCursor
INTO @aliasColumn
END
CLOSE ViewColumnNameCursor;
DEALLOCATE ViewColumnNameCursor;
DROP TABLE #_suppress_output
SELECT *
FROM @viewTableColumnMapping
ORDER BY [ViewAliasColumn]
END
El procedimiento almacenado ahora se puede ejecutar de la siguiente manera:
EXEC dbo.ViewGetColumnDependencies @viewName = ''vTEST''
He hecho algunas investigaciones al respecto, pero todavía no tengo una solución. Lo que quiero obtener es dependencias de nivel de columna en una vista. Entonces, digamos que tenemos una mesa como esta
create table TEST(
first_name varchar(10),
last_name varchar(10),
street varchar(10),
number int
)
y una vista como esta:
create view vTEST
as
select
first_name + '' '' + last_name as [name],
street + '' '' + cast(number as varchar(max)) as [address]
from dbo.TEST
Lo que me gustaría es obtener un resultado como este:
column_name depends_on_column_name depends_on_table_name
----------- --------------------- --------------------
name first_name dbo.TEST
name last_name dbo.TEST
address street dbo.TEST
address number dbo.TEST
He intentado la función sys.dm_sql_referenced_entities , pero referencing_minor_id siempre está 0 allí para las vistas.
select
referencing_minor_id,
referenced_schema_name + ''.'' + referenced_entity_name as depends_on_table_name,
referenced_minor_name as depends_on_column_name
from sys.dm_sql_referenced_entities(''dbo.vTEST'', ''OBJECT'')
referencing_minor_id depends_on_table_name depends_on_column_name
-------------------- --------------------- ----------------------
0 dbo.TEST NULL
0 dbo.TEST first_name
0 dbo.TEST last_name
0 dbo.TEST street
0 dbo.TEST number
Lo mismo es cierto para sys.sql_expression_dependencies y para sys.sql_dependencies obsoletos.
Entonces, ¿echo de menos algo o es imposible de hacer?
Hay algunas preguntas relacionadas (¿ Encontrar el nombre de columna real de un alias usado en una vista? ), Pero como dije, todavía no he encontrado una solución que funcione.
EDIT 1 : he intentado usar DAC para consultar si esta información está almacenada en algún lugar de las Tablas base del sistema pero no la he encontrado
Es una solución basada en plan de consulta. Tiene algunas aventuras
- casi cualquier consulta de selección puede ser procesada
- no SchemaBinding
y desventajas
- no ha sido probado correctamente
- puede romperse repentinamente si Microsoft cambia el plan de consulta XML.
La idea central es que cada expresión de columna dentro del plan de consulta XML se define en el nodo "Valor definido". El primer subnodo de "DefinedValue" es una referencia a la columna de salida y el segundo es una expresión. La expresión calcula a partir de columnas de entrada y valores constantes. Como se mencionó anteriormente, se basa únicamente en la observación empírica y debe probarse adecuadamente.
Es un ejemplo de invocación:
exec dbo.GetColumnDependencies ''select * from dbo.vTEST''
target_column_name | source_column_name | const_value
---------------------------------------------------
address | Expr1007 | NULL
name | Expr1006 | NULL
Expr1006 | NULL | '' ''
Expr1006 | [testdb].[dbo].first_name | NULL
Expr1006 | [testdb].[dbo].last_name | NULL
Expr1007 | NULL | '' ''
Expr1007 | [testdb].[dbo].number | NULL
Expr1007 | [testdb].[dbo].street | NULL
Es codigo En primer lugar obtener el plan de consulta XML.
declare @select_query as varchar(4000) = ''select * from dbo.vTEST'' -- IT''S YOUR QUERY HERE.
declare @select_into_query as varchar(4000) = ''select top (1) * into #foo from ('' + @select_query + '') as src''
, @xml_plan as xml = null
, @xml_generation_tries as tinyint = 10
;
while (@xml_plan is null and @xml_generation_tries > 0) -- There is no guaranty that plan will be cached.
begin
execute (@select_into_query);
select @xml_plan = pln.query_plan
from sys.dm_exec_query_stats as qry
cross apply sys.dm_exec_sql_text(qry.sql_handle) as txt
cross apply sys.dm_exec_query_plan(qry.plan_handle) as pln
where txt.text = @select_into_query
;
end
if (@xml_plan is null
) begin
raiserror(N''Can''''t extract XML query plan from cache.'' ,15 ,0);
return;
end
;
La siguiente es una consulta principal. Su parte más importante es la expresión de tabla común recursiva para la extracción de columnas.
with xmlnamespaces(default ''http://schemas.microsoft.com/sqlserver/2004/07/showplan''
,''http://schemas.microsoft.com/sqlserver/2004/07/showplan'' as shp -- Used in .query() for predictive namespace using.
)
, cte_column_dependencies as
(
La semilla de recursión es una consulta que extrae columnas para la tabla #foo que almacena 1 fila de consultas de selección interesadas.
select
(select foo_col.info.query(''./ColumnReference'') for xml raw(''shp:root'') ,type) -- Becouse .value() can''t extract attribute from root node.
as target_column_info
, (select foo_col.info.query(''./ScalarOperator/Identifier/ColumnReference'') for xml raw(''shp:root'') ,type)
as source_column_info
, cast(null as xml) as const_info
, 1 as iteration_no
from @xml_plan.nodes(''//Update/SetPredicate/ScalarOperator/ScalarExpressionList/ScalarOperator/MultipleAssign/Assign'')
as foo_col(info)
where foo_col.info.exist(''./ColumnReference[@Table="[#foo]"]'') = 1
La parte recursiva busca el nodo "DefinedValue" con una columna dependiente y extrae todos los subnodos "ColumnReference" y "Const" que se usaron en la expresión de columna. Es más complicado por conversiones de XML a SQL.
union all
select
(select internal_col.info.query(''.'') for xml raw(''shp:root'') ,type)
, source_info.column_info
, source_info.const_info
, prev_dependencies.iteration_no + 1
from @xml_plan.nodes(''//DefinedValue/ColumnReference'') as internal_col(info)
inner join cte_column_dependencies as prev_dependencies -- Filters by depended columns.
on prev_dependencies.source_column_info.value(''(//ColumnReference/@Column)[1]'' ,''nvarchar(4000)'') = internal_col.info.value(''(./@Column)[1]'' ,''nvarchar(4000)'')
and exists (select prev_dependencies.source_column_info.value(''(.//@Schema)[1]'' ,''nvarchar(4000)'') intersect select internal_col.info.value(''(./@Schema)[1]'' ,''nvarchar(4000)''))
and exists (select prev_dependencies.source_column_info.value(''(.//@Database)[1]'' ,''nvarchar(4000)'') intersect select internal_col.info.value(''(./@Database)[1]'' ,''nvarchar(4000)''))
and exists (select prev_dependencies.source_column_info.value(''(.//@Server)[1]'' ,''nvarchar(4000)'') intersect select internal_col.info.value(''(./@Server)[1]'' ,''nvarchar(4000)''))
cross apply ( -- Becouse only column or only constant can be places in result row.
select (select source_col.info.query(''.'') for xml raw(''shp:root'') ,type) as column_info
, null as const_info
from internal_col.info.nodes(''..//ColumnReference'') as source_col(info)
union all
select null as column_info
, (select const.info.query(''.'') for xml raw(''shp:root'') ,type) as const_info
from internal_col.info.nodes(''..//Const'') as const(info)
) as source_info
where source_info.column_info is null
or (
-- Except same node selected by ''..//ColumnReference'' from its sources. Sorry, I''m not so well to check it with XQuery simple.
source_info.column_info.value(''(//@Column)[1]'' ,''nvarchar(4000)'') <> internal_col.info.value(''(./@Column)[1]'' ,''nvarchar(4000)'')
and (select source_info.column_info.value(''(//@Schema)[1]'' ,''nvarchar(4000)'') intersect select internal_col.info.value(''(./@Schema)[1]'' ,''nvarchar(4000)'')) is null
and (select source_info.column_info.value(''(//@Database)[1]'' ,''nvarchar(4000)'') intersect select internal_col.info.value(''(./@Database)[1]'' ,''nvarchar(4000)'')) is null
and (select source_info.column_info.value(''(//@Server)[1]'' ,''nvarchar(4000)'') intersect select internal_col.info.value(''(./@Server)[1]'' ,''nvarchar(4000)'')) is null
)
)
Finalmente, es una declaración selecta que convierte XML a texto humano apropiado.
select
-- col_dep.target_column_info
--, col_dep.source_column_info
--, col_dep.const_info
coalesce(col_dep.target_column_info.value(''(.//shp:ColumnReference/@Server)[1]'' ,''nvarchar(4000)'') + ''.'' ,'''')
+ coalesce(col_dep.target_column_info.value(''(.//shp:ColumnReference/@Database)[1]'' ,''nvarchar(4000)'') + ''.'' ,'''')
+ coalesce(col_dep.target_column_info.value(''(.//shp:ColumnReference/@Schema)[1]'' ,''nvarchar(4000)'') + ''.'' ,'''')
+ col_dep.target_column_info.value(''(.//shp:ColumnReference/@Column)[1]'' ,''nvarchar(4000)'')
as target_column_name
, coalesce(col_dep.source_column_info.value(''(.//shp:ColumnReference/@Server)[1]'' ,''nvarchar(4000)'') + ''.'' ,'''')
+ coalesce(col_dep.source_column_info.value(''(.//shp:ColumnReference/@Database)[1]'' ,''nvarchar(4000)'') + ''.'' ,'''')
+ coalesce(col_dep.source_column_info.value(''(.//shp:ColumnReference/@Schema)[1]'' ,''nvarchar(4000)'') + ''.'' ,'''')
+ col_dep.source_column_info.value(''(.//shp:ColumnReference/@Column)[1]'' ,''nvarchar(4000)'')
as source_column_name
, col_dep.const_info.value(''(/shp:root/shp:Const/@ConstValue)[1]'' ,''nvarchar(4000)'')
as const_value
from cte_column_dependencies as col_dep
order by col_dep.iteration_no ,target_column_name ,source_column_name
option (maxrecursion 512) -- It''s an assurance from infinite loop.
Esta solución podría responder a su pregunta sólo parcialmente. No funcionará para las columnas que son expresiones.
Podría usar sys.dm_exec_describe_first_result_set para obtener información de la columna:
@include_browse_information
Si se establece en 1, cada consulta se analiza como si tuviera una opción FOR BROWSE en la consulta. Se devuelven columnas clave adicionales e información de la tabla de origen.
CREATE TABLE txu(id INT, first_name VARCHAR(10), last_name VARCHAR(10));
CREATE TABLE txd(id INT, id_fk INT, address VARCHAR(100));
CREATE VIEW v_txu
AS
SELECT t.id AS PK_id,
t.first_name AS name,
d.address,
t.first_name + t.last_name AS name_full
FROM txu t
JOIN txd d
ON t.id = d.id_fk
Consulta principal:
SELECT name, source_database, source_schema,
source_table, source_column
FROM sys.dm_exec_describe_first_result_set(N''SELECT * FROM v_txu'', null, 1) ;
Salida:
+-----------+--------------------+---------------+--------------+---------------+
| name | source_database | source_schema | source_table | source_column |
+-----------+--------------------+---------------+--------------+---------------+
| PK_id | fiddle_0f9d47226c4 | dbo | txu | id |
| name | fiddle_0f9d47226c4 | dbo | txu | first_name |
| address | fiddle_0f9d47226c4 | dbo | txd | address |
| name_full | null | null | null | null |
+-----------+--------------------+---------------+--------------+---------------+
Estaba jugando con esto, pero no tuve tiempo de ir más lejos. Tal vez esto ayude:
-- Returns all table columns called in the view and the objects they pull from
SELECT
v.[name] AS ViewName
,d.[referencing_id] AS ViewObjectID
,c.[name] AS ColumnNames
,OBJECT_NAME(d.referenced_id) AS ReferencedTableName
,d.referenced_id AS TableObjectIDsReferenced
FROM
sys.views v
INNER JOIN sys.sql_expression_dependencies d ON d.referencing_id = v.[object_id]
INNER JOIN sys.objects o ON d.referencing_id = o.[object_id]
INNER JOIN sys.columns c ON d.referenced_id = c.[object_id]
WHERE v.[name] = ''vTEST''
-- Returns all output columns in the view
SELECT
OBJECT_NAME([object_id]) AS ViewName
,[object_id] AS ViewObjectID
,[name] AS OutputColumnName
FROM sys.columns
WHERE OBJECT_ID(''vTEST'') = [object_id]
-- Get the view definition
SELECT
VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ''vTEST''
Todo lo que necesitas se menciona en la definición de vista.
para que podamos extraer esta información siguiendo los siguientes pasos:
Asigna la definición de vista a una variable de cadena.
Dividirlo con (,) coma.
Dividir el alias con el operador (+) más mediante el uso de CROSS APPLY con XML.
Utilice las tablas del sistema para obtener la información precisa como la tabla original.
Manifestación:-
Create PROC psp_GetLevelDependsView (@sViewName varchar(200))
AS
BEGIN
Declare @stringToSplit nvarchar(1000),
@name NVARCHAR(255),
@dependsTableName NVARCHAR(50),
@pos INT
Declare @returnList TABLE ([Name] [nvarchar] (500))
SELECT TOP 1 @dependsTableName= table_schema + ''.''+ TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
select @stringToSplit = definition
from sys.objects o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( @sViewName)
and o.type = ''V''
WHILE CHARINDEX('','', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX('','', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
select COLUMN_NAME , b.Name as Expression
Into #Temp
FROM INFORMATION_SCHEMA.COLUMNS a , @returnList b
WHERE TABLE_NAME= @sViewName
And (b.Name) like ''%'' + ( COLUMN_NAME) + ''%''
SELECT A.COLUMN_NAME as column_name,
Split.a.value(''.'', ''VARCHAR(100)'') AS depends_on_column_name , @dependsTableName as depends_on_table_name
Into #temp2
FROM
(
SELECT COLUMN_NAME,
CAST (''<M>'' + REPLACE(Expression, ''+'', ''</M><M>'') + ''</M>'' AS XML) AS Data
FROM #Temp
) AS A CROSS APPLY Data.nodes (''/M'') AS Split(a);
SELECT b.column_name , a.COLUMN_NAME as depends_on_column_name , b.depends_on_table_name
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a , #temp2 b
WHERE VIEW_NAME= @sViewName
and b.depends_on_column_name like ''%'' + a.COLUMN_NAME + ''%''
drop table #Temp
drop table #Temp2
END
Prueba:-
exec psp_GetLevelDependsView ''vTest''
Resultado:-
column_name depends_on_column_name depends_on_table_name
----------- --------------------- --------------------
name first_name dbo.TEST
name last_name dbo.TEST
address street dbo.TEST
address number dbo.TEST