repo_sys.sql_expression_dependencies - V

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

RepoObject_guid: 6190291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo_sys.sql_expression_dependencies

Columns

Table 1. Columns of repo_sys.sql_expression_dependencies - V
PK Column Name Data Type NULL? ID

nvarchar(max)

NULL

bit

NOT NULL

bit

NOT NULL

bit

NULL

bit

NOT NULL

tinyint

NULL

nvarchar(60)

NULL

sysname

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

int

NULL

int

NOT NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

char(2)

NULL

nvarchar(60)

NULL

tinyint

NULL

nvarchar(60)

NULL

sysname

NULL

nvarchar(128)

NULL

int

NOT NULL

int

NOT NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

char(2)

NULL

nvarchar(60)

NULL

Foreign Key Diagram

entity_1_1_fk-repo_sys.sql_expression_dependencies

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo_sys.sql_expression_dependencies

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo_sys.sql_expression_dependencies

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo_sys.sql_expression_dependencies

Column Reference Diagram

entity_1_1_colref-repo_sys.sql_expression_dependencies

Indexes

idx_sql_expression_dependencies__1

idx_sql_expression_dependencies__2

idx_sql_expression_dependencies__3

idx_sql_expression_dependencies__4

Column Details

_

definition

definition

nvarchar(max)

NULL

is_ambiguous

is_ambiguous

bit

NOT NULL

is_caller_dependent

is_caller_dependent

bit

NOT NULL

is_schema_bound_reference

is_schema_bound_reference

bit

NOT NULL

referenced_class

referenced_class

tinyint

NULL

referenced_class_desc

referenced_class_desc

nvarchar(60)

NULL

referenced_column_name

referenced_column_name

sysname

NULL

referenced_database_name

referenced_database_name

nvarchar(128)

NULL

referenced_minor_id

referenced_minor_id

int

NOT NULL

referenced_RepoObjectColumn_guid

referenced_RepoObjectColumn_guid

uniqueidentifier

NULL

referenced_server_name

referenced_server_name

nvarchar(128)

NULL

referenced_type_desciption

referenced_type_desciption

nvarchar(60)

NULL

referencing_class

referencing_class

tinyint

NULL

referencing_class_desc

referencing_class_desc

nvarchar(60)

NULL

referencing_column_name

referencing_column_name

sysname

NULL

referencing_minor_id

referencing_minor_id

int

NOT NULL

referencing_RepoObjectColumn_guid

referencing_RepoObjectColumn_guid

uniqueidentifier

NULL

referencing_type_desciption

referencing_type_desciption

nvarchar(60)

NULL

sql_modules_definition

repo_sys.sql_expression_dependencies - V script
/*
ATTENTION:
[SysObject_RepoObject_guid] has prefix SysObject, because it it the RepoObject_guid stored in repo_sys.extended_properties
but some objects havn't extended properties, for example Triggers
These objects have RepoObject_guid only in [repo].[RepoObject].RepoObject_guid, but they have no SysObject_RepoObject_guid

Also if the parameter dwh_readonly = 0 is set, there is no SysObject_RepoObject_guid

Therefore the join is not done with repo_sys.SysObject, but with [repo].[SysObject_RepoObject_via_name].

*/
CREATE View repo_sys.sql_expression_dependencies
As
--
Select
    sed.referencing_id
  , sed.referencing_minor_id
  , sed.referenced_class
  , sed.referenced_id
  , sed.referenced_minor_id
  , referencing_schema_name           = Object_Schema_Name ( sed.referencing_id, db.dwh_database_id )
  , referencing_entity_name           = Object_Name ( sed.referencing_id, db.dwh_database_id )
  --, COL_NAME([sed].[referencing_id] , [sed].[referencing_minor_id]) AS [referencing_column_name]
  , referencing_column_name           = ssc.SysObject_column_name
  , referencing_type                  = so.SysObject_type
  , referencing_type_desciption       = so.SysObject_type_desc
  , referencing_RepoObject_guid       = so.RepoObject_guid
  , referencing_RepoObjectColumn_guid = ssc.RepoObjectColumn_guid
  , sed.referencing_class
  , sed.referencing_class_desc
  , sed.referenced_server_name
  , referenced_database_name          = sed.referenced_database_name Collate Database_Default
  , referenced_schema_name            = sed.referenced_schema_name Collate Database_Default
  , referenced_entity_name            = sed.referenced_entity_name Collate Database_Default
  --, COL_NAME([sed].[referenced_id] , [sed].[referenced_minor_id]) AS   [referenced_column_name]
  , referenced_column_name            = ssc2.SysObject_column_name
  , sed.referenced_class_desc
  , referenced_type                   = so2.SysObject_type
  , referenced_type_desciption        = so2.SysObject_type_desc
  , referenced_RepoObject_guid        = so2.RepoObject_guid
  , referenced_RepoObjectColumn_guid  = ssc2.RepoObjectColumn_guid
  , sed.is_schema_bound_reference
  , sed.is_caller_dependent
  , sed.is_ambiguous
  --table columns can be is_computed = 1, these columns should also have a defintion
  , ssc.is_computed
  , ssc.definition
From
    sys_dwh.sql_expression_dependencies          As sed
    Inner Join
        repo.SysObject_RepoObject_via_name       As so
            On
            sed.referencing_id = so.SysObject_id

    Left Join
        repo.SysObject_RepoObject_via_name       As so2
            On
            sed.referenced_id = so2.SysObject_id

    Left Join
        repo.SysColumn_RepoObjectColumn_via_name As ssc
            On
            sed.referencing_id = ssc.SysObject_id
            And sed.referencing_minor_id = ssc.SysObject_column_id

    Left Join
        repo.SysColumn_RepoObjectColumn_via_name As ssc2
            On
            sed.referenced_id = ssc2.SysObject_id
            And sed.referenced_minor_id = ssc2.SysObject_column_id
    --
    Cross Apply config.ftv_dwh_database ()       As db