repo_sys.ForeignKeyColumn - V

type: V ( view ), modify_date: 2021-08-17 20:00:13

RepoObject_guid: 0865AE67-BC72-EB11-84E3-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo_sys.foreignkeycolumn

Columns

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

int

NOT NULL

int

NOT NULL

nvarchar(261)

NULL

uniqueidentifier

NULL

sysname

NULL

int

NOT NULL

int

NOT NULL

int

NOT NULL

sysname

NULL

nvarchar(128)

NULL

int

NOT NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

sysname

NULL

nvarchar(128)

NULL

nvarchar(261)

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

nvarchar(261)

NULL

Foreign Key Diagram

entity_1_1_fk-repo_sys.foreignkeycolumn

References

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-repo_sys.foreignkeycolumn

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo_sys.foreignkeycolumn

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo_sys.foreignkeycolumn

Column Reference Diagram

entity_1_1_colref-repo_sys.foreignkeycolumn

Indexes

idx_ForeignKeyColumn__1

Column Details

_

constraint_column_id

constraint_column_id

int

NOT NULL

constraint_object_id

constraint_object_id

int

NOT NULL

ForeignKey_fullname

ForeignKey_fullname

nvarchar(261)

NULL

Description

(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))


ForeignKey_guid

ForeignKey_guid

uniqueidentifier

NULL

ForeignKey_name

ForeignKey_name

sysname

NULL

Referenced Columns

parent_column_id

parent_column_id

int

NOT NULL

parent_object_id

parent_object_id

int

NOT NULL

referenced_column_id

referenced_column_id

int

NOT NULL

referenced_column_name

referenced_column_name

sysname

NULL

referenced_name

referenced_name

nvarchar(128)

NULL

referenced_object_id

referenced_object_id

int

NOT 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

referencing_column_name

referencing_column_name

sysname

NULL

referencing_name

referencing_name

nvarchar(128)

NULL

referencing_RepoObject_fullname

referencing_RepoObject_fullname

nvarchar(261)

NULL

Description

(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))


referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NULL

referencing_RepoObjectColumn_guid

referencing_RepoObjectColumn_guid

uniqueidentifier

NULL

referencing_schema_name

referencing_schema_name

nvarchar(128)

NULL

referencing_SysObject_fullname

referencing_SysObject_fullname

nvarchar(261)

NULL

Description

(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))


sql_modules_definition

repo_sys.ForeignKeyColumn - V script
CREATE View repo_sys.ForeignKeyColumn
As
Select
    --
    fkc.constraint_object_id
  , fkc.constraint_column_id
  , fkc.parent_object_id
  , fkc.parent_column_id
  , fkc.referenced_object_id
  , fkc.referenced_column_id
  , ForeignKey_name                   = fk.name
  , fk.ForeignKey_fullname
  , fk.ForeignKey_guid
  , referencing_column_name           = parent_sc.SysObject_column_name
  , referencing_name                  = parent_sc.SysObject_name
  , referencing_schema_name           = parent_sc.SysObject_schema_name
  , referencing_RepoObject_fullname   = fk.parent_RepoObject_fullname
  , referencing_RepoObject_guid       = parent_sc.SysObject_RepoObject_guid
  , referencing_RepoObjectColumn_guid = parent_sc.SysObject_RepoObjectColumn_guid
  , referencing_SysObject_fullname    = fk.parent_SysObject_fullname
  , referenced_column_name            = referenced_sc.SysObject_column_name
  , referenced_name                   = referenced_sc.SysObject_name
  , referenced_schema_name            = referenced_sc.SysObject_schema_name
  , referenced_RepoObject_guid        = referenced_sc.SysObject_RepoObject_guid
  , referenced_RepoObjectColumn_guid  = referenced_sc.SysObject_RepoObjectColumn_guid
From
    sys_dwh.foreign_key_columns As fkc
    Left Outer Join
        repo_sys.ForeignKey     As fk
            On
            fk.object_id                          = fkc.constraint_object_id

    Left Outer Join
        repo_sys.SysColumn      As parent_sc
            On
            parent_sc.SysObject_id                = fkc.parent_object_id
            And parent_sc.SysObject_column_id     = fkc.parent_column_id

    Left Outer Join
        repo_sys.SysColumn      As referenced_sc
            On
            referenced_sc.SysObject_id            = fkc.referenced_object_id
            And referenced_sc.SysObject_column_id = fkc.referenced_column_id