reference.RepoObjectColumn_reference_QueryPlan - V

type: V ( view ), modify_date: 2022-01-05 18:06:34

RepoObject_guid: 6C90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-reference.repoobjectcolumn_reference_queryplan

Columns

Table 1. Columns of reference.RepoObjectColumn_reference_QueryPlan - V
PK Column Name Data Type NULL? ID

1

int

NULL

2

int

NULL

3

int

NULL

4

int

NULL

nvarchar(max)

NULL

varchar(10)

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

bigint

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

char(2)

NULL

nvarchar(128)

NULL

nvarchar(128)

NOT NULL

bigint

NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NULL

nvarchar(128)

NOT NULL

char(2)

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

Foreign Key Diagram

entity_1_1_fk-reference.repoobjectcolumn_reference_queryplan

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.repoobjectcolumn_reference_queryplan

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.repoobjectcolumn_reference_queryplan

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.repoobjectcolumn_reference_queryplan

Column Reference Diagram

entity_1_1_colref-reference.repoobjectcolumn_reference_queryplan

Indexes

PK_RepoObjectColumn_reference_QueryPlan

idx_RepoObjectColumn_reference_QueryPlan__1

idx_RepoObjectColumn_reference_QueryPlan__2

idx_RepoObjectColumn_reference_QueryPlan__4

idx_RepoObjectColumn_reference_QueryPlan__5

idx_RepoObjectColumn_reference_QueryPlan__6

idx_RepoObjectColumn_reference_QueryPlan__7

Column Details

_

referencing_id

1

referencing_id

int

NULL

Referenced Columns

referencing_minor_id

2

referencing_minor_id

int

NULL

referenced_id

3

referenced_id

int

NULL

referenced_minor_id

4

referenced_minor_id

int

NULL

definition

definition

nvarchar(max)

NULL

InformationSource

InformationSource

varchar(10)

NOT NULL

is_computed

is_computed

bit

NULL

is_referenced_object

is_referenced_object

bit

NULL

is_referencing_object_equal_referenced_object

is_referencing_object_equal_referenced_object

bit

NULL

is_source_column_name_expression

is_source_column_name_expression

bit

NULL

Description

(CONVERT([bit],case when [source_column_name] like 'EXPR[0-9][0-9][0-9][0-9]' then (1) else (0) end))


is_target_column_name_expression

is_target_column_name_expression

bit

NULL

Description

(CONVERT([bit],case when [target_column_name] like 'EXPR[0-9][0-9][0-9][0-9]' then (1) else (0) end))


referenced_column_name

referenced_column_name

nvarchar(128)

NULL

referenced_entity_name

referenced_entity_name

nvarchar(128)

NULL

referenced_node_id

referenced_node_id

bigint

NULL

referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NULL

referenced_RepoObjectColumn_guid

referenced_RepoObjectColumn_guid

uniqueidentifier

NULL

referenced_schema_name

referenced_schema_name

nvarchar(128)

NULL

referenced_type

referenced_type

char(2)

NULL

referencing_column_name

referencing_column_name

nvarchar(128)

NULL

referencing_entity_name

referencing_entity_name

nvarchar(128)

NOT NULL

Referenced Columns

referencing_node_id

referencing_node_id

bigint

NULL

referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NOT NULL

referencing_RepoObjectColumn_guid

referencing_RepoObjectColumn_guid

uniqueidentifier

NULL

referencing_schema_name

referencing_schema_name

nvarchar(128)

NOT NULL

referencing_type

referencing_type

char(2)

NULL

Referenced Columns

source_schema_name_quoted

source_schema_name_quoted

nvarchar(128)

NULL

source_table_name_quoted

source_table_name_quoted

nvarchar(128)

NULL

sql_modules_definition

reference.RepoObjectColumn_reference_QueryPlan - V script
CREATE View [reference].[RepoObjectColumn_reference_QueryPlan]
As
--
Select
    referencing_id                                = ro.SysObject_id
  , referencing_minor_id                          = roc.SysObjectColumn_column_id
  , referencing_node_id                           = Cast(ro.SysObject_id As BigInt) * 10000 + roc.SysObjectColumn_column_id
  , referenced_id                                 = roc2.SysObject_id
  , referenced_minor_id                           = roc2.SysObjectColumn_column_id
  , referenced_node_id                            = Cast(roc2.SysObject_id As BigInt) * 10000 + roc2.SysObjectColumn_column_id
  , referencing_RepoObject_guid                   = ros.RepoObject_guid
  , referencing_RepoObjectColumn_guid             = roc.RepoObjectColumn_guid
  , referenced_RepoObject_guid                    = roc2.RepoObject_guid
  , referenced_RepoObjectColumn_guid              = roc2.RepoObjectColumn_guid
  , referencing_type                              = ro.SysObject_type
  , referencing_schema_name                       = ro.SysObject_schema_name
  , referencing_entity_name                       = ro.SysObject_name
  , referencing_column_name                       = ros.target_column_name
  , referenced_schema_name                        = roc2.SysObject_schema_name
  , referenced_entity_name                        = roc2.SysObject_name
  , referenced_column_name                        = ros.source_column_name
  , referenced_type                               = roc2.SysObject_type
  , InformationSource                             = 'query plan'
  ----, [ros].[source_server_name] AS      [source_server_name]
  ----, [repo].[fs_dwh_database_name]() AS [source_database_name]
  --, [ros].[source_database_name] AS      [source_database_name_quoted]
  , source_schema_name_quoted                     = ros.source_schema_name
  , source_table_name_quoted                      = ros.source_table_name
  , ros.is_target_column_name_expression
  , ros.is_source_column_name_expression
  , is_computed                                   = Cast(Case
                                                             When ros.is_target_column_name_expression = 1
                                                                  Or ros.is_source_column_name_expression = 1
                                                                 Then
                                                                 1
                                                             Else
                                                                 0
                                                         End As Bit)
  , definition                                    = Cast(Null As NVarchar(Max))
  , is_referencing_object_equal_referenced_object = Cast(Case
                                                             When ros.RepoObject_guid = roc2.RepoObject_guid
                                                                 Then
                                                                 1
                                                             Else
                                                                 0
                                                         End As Bit)
  , is_referenced_object                          =
    (
        Select
            Top 1
            Cast(1 As Bit)
        From
            reference.RepoObject_reference_T As ro_r
        Where
            ro_r.referencing_RepoObject_guid    = ros.RepoObject_guid
            And ro_r.referenced_RepoObject_guid = roc2.RepoObject_guid
    )
From
    reference.RepoObjectSource_QueryPlan  As ros
    Inner Join
        repo.RepoObject                   As ro
            On
            ros.RepoObject_guid = ro.RepoObject_guid
    Cross Join config.ftv_dwh_database () As dwhdb
    Left Join
        repo.RepoObjectColumn_gross As roc
            On
            ro.SysObject_schema_name                     = roc.SysObject_schema_name
            And ro.SysObject_name                        = roc.SysObject_name
            And ros.target_column_name                   = roc.SysObjectColumn_name

    Left Join
        repo.RepoObjectColumn_gross As roc2
            On
            ros.source_column_name                       = roc2.SysObjectColumn_name
            And ros.source_server_name Is Null
            And
            (
                (
                    ros.source_database_name             = QuoteName ( dwhdb.dwh_database_name )
                    And ros.source_schema_name           = QuoteName ( roc2.SysObject_schema_name )
                    And ros.source_table_name            = QuoteName ( roc2.SysObject_name )
                )
                --if source_column is expression like 'Expr1006' then these are missing: ros.source_schema_name, ros.source_table_name
                --we should use these names from the target column (which is in the same object)
                Or
                (
                    ros.is_source_column_name_expression = 1
                    And ro.SysObject_schema_name         = roc2.SysObject_schema_name
                    And ro.SysObject_name                = roc2.SysObject_name
                )
            )
--
--
Where
    --exclude reference on self (target column = source column)
    Not (
            ro.SysObject_schema_name = roc2.SysObject_schema_name
            And ro.SysObject_name = roc2.SysObject_name
            And ros.target_column_name = ros.source_column_name
        )
------
--AND --
--[ros].[RepoObject_guid] = '6076940B-2B57-EB11-84D8-A81E8446D5B0'