reference.RepoObject_reference_SqlExpressionDependencies - V

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

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

Description

Examples

Entity Diagram

entity-reference.repoobject_reference_sqlexpressiondependencies

Columns

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

1

uniqueidentifier

NULL

2

uniqueidentifier

NULL

varchar(31)

NOT NULL

nvarchar(128)

NULL

nvarchar(517)

NULL

int

NULL

nvarchar(128)

NULL

char(2)

NULL

nvarchar(128)

NULL

nvarchar(517)

NULL

int

NOT NULL

nvarchar(128)

NULL

char(2)

NULL

Foreign Key Diagram

entity_1_1_fk-reference.repoobject_reference_sqlexpressiondependencies

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.repoobject_reference_sqlexpressiondependencies

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.repoobject_reference_sqlexpressiondependencies

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.repoobject_reference_sqlexpressiondependencies

Column Reference Diagram

entity_1_1_colref-reference.repoobject_reference_sqlexpressiondependencies

Indexes

PK_RepoObject_reference_SqlExpressionDependencies

idx_RepoObject_reference_SqlExpressionDependencies__2

idx_RepoObject_reference_SqlExpressionDependencies__3

Column Details

_

referenced_RepoObject_guid

1

referenced_RepoObject_guid

uniqueidentifier

NULL

referencing_RepoObject_guid

2

referencing_RepoObject_guid

uniqueidentifier

NULL

InformationSource

InformationSource

varchar(31)

NOT NULL

referenced_entity_name

referenced_entity_name

nvarchar(128)

NULL

referenced_fullname

referenced_fullname

nvarchar(517)

NULL

referenced_id

referenced_id

int

NULL

referenced_schema_name

referenced_schema_name

nvarchar(128)

NULL

referenced_type

referenced_type

char(2)

NULL

referencing_entity_name

referencing_entity_name

nvarchar(128)

NULL

referencing_fullname

referencing_fullname

nvarchar(517)

NULL

referencing_id

referencing_id

int

NOT NULL

referencing_schema_name

referencing_schema_name

nvarchar(128)

NULL

referencing_type

referencing_type

char(2)

NULL

sql_modules_definition

reference.RepoObject_reference_SqlExpressionDependencies - V script
/*
issue in [sys].[sql_expression_dependencies]
After changing the case of names or schemas some content has wrong case
this generates errors in PUML graphics

referenced_schema_name
referenced_entity_name
referencing_schema_name
referencing_entity_name

*/
CREATE View reference.RepoObject_reference_SqlExpressionDependencies
As
Select
    sed.referenced_id
  , sed.referencing_id
  --, [sed].[referencing_minor_id]
  , sed.referenced_entity_name
  , referenced_fullname  = Coalesce (
                                        ro1.RepoObject_fullname
                                      , Concat (
                                                   QuoteName ( sed.referenced_schema_name )
                                                 , '.'
                                                 , QuoteName ( sed.referenced_entity_name )
                                               )
                                    )
  --, [sed].[referenced_minor_id]
  --, Cast(sed.referenced_id As BigInt) * 10000  As referenced_node_id
  , sed.referenced_RepoObject_guid
  , sed.referenced_schema_name
  , sed.referenced_type
  --, [sed].[referenced_column_name]
  --, [sed].[referenced_RepoObjectColumn_guid]
  , sed.referencing_entity_name
  , referencing_fullname = Coalesce (
                                        ro2.RepoObject_fullname
                                      , Concat (
                                                   QuoteName ( sed.referencing_schema_name )
                                                 , '.'
                                                 , QuoteName ( sed.referencing_entity_name )
                                               )
                                    )
  --, Cast(sed.referencing_id As BigInt) * 10000 As referencing_node_id
  , sed.referencing_RepoObject_guid
  , sed.referencing_schema_name
  , sed.referencing_type
  --, [sed].[referencing_RepoObjectColumn_guid]
  --, [sed].[referencing_column_name]
  , InformationSource    = 'sys.sql_expression_dependencies'
--, [sed].[referenced_server_name]
--, [sed].[referenced_database_name]
--, [sed].[referenced_class]
--, [sed].[referencing_class]
--, [sed].[referencing_class_desc]
--, [sed].[referenced_class_desc]
--, [sed].[referencing_type_desciption]
--, [sed].[referenced_type_desciption]
--, [sed].[is_schema_bound_reference]
--, [sed].[is_caller_dependent]
--, [sed].[is_ambiguous]
From
    repo_sys.sql_expression_dependencies As sed
    Left Join
        repo.RepoObject                  As ro1
            On
            ro1.RepoObject_guid = sed.referenced_RepoObject_guid

    Left Join
        repo.RepoObject                  As ro2
            On
            ro1.RepoObject_guid = sed.referencing_RepoObject_guid
Where
    --object level
    sed.referencing_minor_id    = 0
    And sed.referenced_minor_id = 0
    --exclude virtual objects (like expressions used in procedures)
    --or objects without extended properties (like triggers)
    --currently:
    --[RepoObject_guid] = [sed].[referencing_RepoObject_guid]
    --and [sed].[referencing_RepoObject_guid] = SysObject_RepoObject_guid
    --these are RepoObject_guid storred in extended properties
    And Not sed.referencing_RepoObject_guid Is Null
    And Not sed.referenced_RepoObject_guid Is Null