reference.RepoObjectColumn_reference_union - V
type: V ( view ), modify_date: 2022-01-05 18:06:35
RepoObject_guid: 7590291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Column Details
_
is_referencing_object_equal_referenced_object
is_referencing_object_equal_referenced_object |
|
|
referenced_RepoObjectColumn_guid
referenced_RepoObjectColumn_guid |
|
|
Referencing Columns
sql_modules_definition
reference.RepoObjectColumn_reference_union - V script
/*
check
SELECT
[referencing_id]
, [referencing_minor_id]
, [InformationSource]
, [referencing_schema_name]
, [referencing_entity_name]
, [referencing_column_name]
, [referencing_type]
, [referenced_schema_name]
, [referenced_entity_name]
, [referenced_column_name]
, [referenced_type]
, [referencing_RepoObject_guid]
, [referencing_RepoObjectColumn_guid]
, [referenced_RepoObject_guid]
, [referenced_RepoObjectColumn_guid]
--, [referencing_node_id]
--, [referenced_id]
--, [referenced_minor_id]
--, [referenced_node_id]
FROM
[repo].[RepoObjectColumn_reference__union]
ORDER BY
[referencing_id]
, [referencing_minor_id]
, [referenced_id]
, [referenced_minor_id]
, [InformationSource]
*/
CREATE View reference.RepoObjectColumn_reference_union
As
--
--repo.RepoObjectColumn_reference__sql_expression_dependencies
--contains calculated columns
--maybe colums in case of SCHEMA_BINDING (not tested)
Select
--
referenced_schema_name
, referenced_entity_name
, referenced_column_name
, referencing_schema_name
, referencing_entity_name
, referencing_column_name
, referencing_id
--, referencing_minor_id
--, referencing_node_id
, referenced_id
--, referenced_minor_id
--, referenced_node_id
, referencing_RepoObject_guid
, referencing_RepoObjectColumn_guid
, referenced_RepoObject_guid
, referenced_RepoObjectColumn_guid
, referencing_type
, referenced_type
, InformationSource
, is_computed
, definition
, is_referencing_object_equal_referenced_object
, is_referenced_object
, referenced_external_AntoraComponent = Null
, referenced_external_AntoraModule = Null
, referenced_is_external = Cast(Null As TinyInt)
, referencing_external_AntoraComponent = Null
, referencing_external_AntoraModule = Null
, referencing_is_external = Cast(Null As TinyInt)
From
reference.RepoObjectColumn_reference_SqlExpressionDependencies
--[repo].[RepoObjectColumn_reference__persistence]
--contains virtual references for persistence tables
--these references can't exist in the real database but only in the repository
Union All
Select
--
referenced_schema_name
, referenced_entity_name
, referenced_column_name
, referencing_schema_name
, referencing_entity_name
, referencing_column_name
, referencing_id
--, referencing_minor_id
--, referencing_node_id
, referenced_id
--, referenced_minor_id
--, referenced_node_id
, referencing_RepoObject_guid
, referencing_RepoObjectColumn_guid
, referenced_RepoObject_guid
, referenced_RepoObjectColumn_guid
, referencing_type
, referenced_type
, InformationSource
, is_computed
, definition
, is_referencing_object_equal_referenced_object
, is_referenced_object
, referenced_external_AntoraComponent = Null
, referenced_external_AntoraModule = Null
, referenced_is_external = Null
, referencing_external_AntoraComponent = Null
, referencing_external_AntoraModule = Null
, referencing_is_external = Null
From
reference.RepoObjectColumn_reference_Persistence
Union All
Select
--
referenced_schema_name
, referenced_entity_name
, referenced_column_name
, referencing_schema_name
, referencing_entity_name
, referencing_column_name
, referencing_id
--, referencing_minor_id
--, referencing_node_id
, referenced_id
--, referenced_minor_id
--, referenced_node_id
, referencing_RepoObject_guid
, referencing_RepoObjectColumn_guid
, referenced_RepoObject_guid
, referenced_RepoObjectColumn_guid
, referencing_type
, referenced_type
, InformationSource
, is_computed
, definition
, is_referencing_object_equal_referenced_object
, is_referenced_object
, referenced_external_AntoraComponent = Null
, referenced_external_AntoraModule = Null
, referenced_is_external = Null
, referencing_external_AntoraComponent = Null
, referencing_external_AntoraModule = Null
, referencing_is_external = Null
From
reference.RepoObjectColumn_reference_SqlModules
Union All
Select
--
referenced_schema_name
, referenced_entity_name
, referenced_column_name
, referencing_schema_name
, referencing_entity_name
, referencing_column_name
, referencing_id
--, referencing_minor_id
--, referencing_node_id
, referenced_id
--, referenced_minor_id
--, referenced_node_id
, referencing_RepoObject_guid
, referencing_RepoObjectColumn_guid
, referenced_RepoObject_guid
, referenced_RepoObjectColumn_guid
, referencing_type
, referenced_type
, InformationSource
, is_computed
, definition
, is_referencing_object_equal_referenced_object
, is_referenced_object
, referenced_external_AntoraComponent = Null
, referenced_external_AntoraModule = Null
, referenced_is_external = Null
, referencing_external_AntoraComponent = Null
, referencing_external_AntoraModule = Null
, referencing_is_external = Null
From
reference.RepoObjectColumn_reference_virtual
Union All
Select
--
referenced_schema_name
, referenced_entity_name
, referenced_column_name
, referencing_schema_name
, referencing_entity_name
, referencing_column_name
, referencing_id
--, referencing_minor_id
--, referencing_node_id
, referenced_id
--, referenced_minor_id
--, referenced_node_id
, referencing_RepoObject_guid
, referencing_RepoObjectColumn_guid
, referenced_RepoObject_guid
, referenced_RepoObjectColumn_guid
, referencing_type
, referenced_type
, InformationSource
, is_computed
, definition
, is_referencing_object_equal_referenced_object
, is_referenced_object
, referenced_external_AntoraComponent
, referenced_external_AntoraModule
, referenced_is_external
, referencing_external_AntoraComponent
, referencing_external_AntoraModule
, referencing_is_external
From
reference.RepoObjectColumn_reference_additional
--UNION ALL
----repo.RepoObjectColumn_reference__first_result_set
----"common" references
----these should be "common" columns in views, not containing expressions
----Attention: views on views are "resolved" like views on the underlaying tables!
----it looks like we don't get references between views here!
----we filter by [is_referenced_object] = 1
----to get only referenced columns from referenced objects
--SELECT [referencing_id]
-- , [referencing_minor_id]
-- , [referencing_node_id]
-- , [referenced_id]
-- , [referenced_minor_id]
-- , [referenced_node_id]
-- , [referencing_RepoObject_guid]
-- , [referencing_RepoObjectColumn_guid]
-- , [referenced_RepoObject_guid]
-- , [referenced_RepoObjectColumn_guid]
-- , [referencing_type]
-- , [referencing_schema_name]
-- , [referencing_entity_name]
-- , [referencing_column_name]
-- , [referenced_schema_name]
-- , [referenced_entity_name]
-- , [referenced_column_name]
-- , [referenced_type]
-- , [InformationSource]
-- , [is_computed] = CAST(0 AS BIT)
-- , [definition] = NULL
-- , [is_referencing_object_equal_referenced_object]
-- , [is_referenced_object]
--FROM repo.[RepoObjectColumn_reference_FirstResultSet]
--WHERE [is_referenced_object] = 1
--UNION ALL
--SELECT [referencing_id]
-- , [referencing_minor_id]
-- , [referencing_node_id]
-- , [referenced_id]
-- , [referenced_minor_id]
-- , [referenced_node_id]
-- , [referencing_RepoObject_guid]
-- , [referencing_RepoObjectColumn_guid]
-- , [referenced_RepoObject_guid]
-- , [referenced_RepoObjectColumn_guid]
-- , [referencing_type]
-- , [referencing_schema_name]
-- , [referencing_entity_name]
-- , [referencing_column_name]
-- , [referenced_schema_name]
-- , [referenced_entity_name]
-- , [referenced_column_name]
-- , [referenced_type]
-- , [InformationSource]
-- , [is_computed] = CAST(0 AS BIT)
-- , [definition] = NULL
-- , [is_referencing_object_equal_referenced_object]
-- , [is_referenced_object]
--FROM repo.[RepoObjectColumn_reference_BySamePredecessors]
---- additional references for view columns, which are not common columns
----the result is not yet OK
--UNION ALL
--SELECT [referencing_id]
-- , [referencing_minor_id]
-- , [referencing_node_id]
-- , [referenced_id]
-- , [referenced_minor_id]
-- , [referenced_node_id]
-- , [referencing_RepoObject_guid]
-- , [referencing_RepoObjectColumn_guid]
-- , [referenced_RepoObject_guid]
-- , [referenced_RepoObjectColumn_guid]
-- , [referencing_type]
-- , [referencing_schema_name]
-- , [referencing_entity_name]
-- , [referencing_column_name]
-- , [referenced_schema_name]
-- , [referenced_entity_name]
-- , [referenced_column_name]
-- , [referenced_type]
-- , [InformationSource]
-- , [is_computed]
-- , [definition]
-- , [is_referencing_object_equal_referenced_object]
-- , [is_referenced_object]
--FROM [repo].[RepoObjectColumn_reference_QueryPlan] AS roc_r
--WHERE
-- --only views
-- [referencing_type] = 'V'
-- --not common, they should be an expresssion or based on an expression
-- AND [is_computed] = 1
-- AND (
-- --these are expressions, which belongs to columns in an referenced object
-- --we want to include view_1.aaa -> table_1.aaa
-- --but this should be excluded: view_2.aaa -> table_1.aaa
-- [is_referenced_object] = 1
-- --expressions can reference a predecessor of a referenced object
-- --but how to handle these columns?
-- --for example:
-- --dbo.view_2.Expr2005 -> dbo.table_1.bbb
-- --but the direct expression should be dbo.view_2.bbb -> dbo.view_1.bbb
-- --we could try to use same-predecessor-logic
-- OR [is_target_column_name_expression] = 1
-- --these are references to expressions, and by definition they are virtually created in the referenced object
-- OR is_referencing_object_equal_referenced_object = 1
-- --
-- )
-- --exclude references from the query above
-- --if referenced columns exists there we will not use any possible expression
-- --because an expression sometimes is also used in case the table column is some special, for example if it is an Auto ID
-- AND NOT EXISTS (
-- SELECT 1
-- FROM [repo].[RepoObjectColumn_reference_FirstResultSet] AS [roc_r_common]
-- WHERE [is_referenced_object] = 1
-- AND [roc_r_common].[referencing_RepoObject_guid] = [roc_r].[referencing_RepoObject_guid]
-- AND [roc_r_common].[referencing_RepoObjectColumn_guid] = [roc_r].[referencing_RepoObjectColumn_guid]
-- )
-- --exclude references from the another query above
-- AND NOT EXISTS (
-- SELECT 1
-- FROM [repo].[RepoObjectColumn_reference_BySamePredecessors] AS [roc_r_derived]
-- WHERE [roc_r_derived].[referencing_RepoObject_guid] = [roc_r].[referencing_RepoObject_guid]
-- AND [roc_r_derived].[referencing_RepoObjectColumn_guid] = [roc_r].[referencing_RepoObjectColumn_guid]
-- )