repo_sys.SysObject - V

type: V ( view ), modify_date: 2021-08-17 20:04:05

RepoObject_guid: 4F90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo_sys.sysobject

Columns

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

datetime

NOT NULL

int

NULL

bit

NOT NULL

bit

NOT NULL

bit

NOT NULL

int

NULL

datetime

NOT NULL

int

NOT NULL

int

NULL

int

NOT NULL

nvarchar(max)

NULL

nvarchar(517)

NOT NULL

nvarchar(257)

NOT NULL

int

NOT NULL

sysname

NOT NULL

uniqueidentifier

NULL

sysname

NULL

tinyint

NULL

nvarchar(60)

NULL

char(2)

NULL

nvarchar(60)

NULL

Foreign Key Diagram

entity_1_1_fk-repo_sys.sysobject

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo_sys.sysobject

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo_sys.sysobject

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo_sys.sysobject

Column Reference Diagram

entity_1_1_colref-repo_sys.sysobject

Column Details

_

create_date

create_date

datetime

NOT NULL

is_ms_shipped

is_ms_shipped

bit

NOT NULL

is_published

is_published

bit

NOT NULL

is_schema_published

is_schema_published

bit

NOT NULL

max_column_id_used

max_column_id_used

int

NULL

parent_object_id

parent_object_id

int

NOT NULL

principal_id

principal_id

int

NULL

schema_id

schema_id

int

NOT NULL

sql_modules_definition

sql_modules_definition

nvarchar(max)

NULL

SysObject_fullname

SysObject_fullname

nvarchar(517)

NOT NULL

SysObject_fullname2

SysObject_fullname2

nvarchar(257)

NOT NULL

temporal_type

temporal_type

tinyint

NULL

temporal_type_desc

temporal_type_desc

nvarchar(60)

NULL

sql_modules_definition

repo_sys.SysObject - 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

ATTENTION:
when using synonyms
SCHEMA_NAME([so].schema_id) doesn't return the original schema_name but it runs in the current database

OBJECT_SCHEMA_NAME muss verwendet werden und die database_id der DWH-Datenbank muss verwendet werden
oder ├╝ber synonym [sys_dwh].schemas
*/
CREATE View repo_sys.SysObject
As
--
Select
    SysObject_id              = so.object_id
  , SysObject_schema_name     = sch.name Collate Database_Default
  --, OBJECT_SCHEMA_NAME([so].object_id) AS [SysObject_schema_name_]
  , SysObject_name            = so.name Collate Database_Default
  , SysObject_fullname        = Concat ( QuoteName ( sch.name ), '.', QuoteName ( so.name )) Collate Database_Default
  , SysObject_fullname2       = Concat ( sch.name, '.', so.name ) Collate Database_Default
  , SysObject_RepoObject_guid = Try_Cast(ep.property_value As UniqueIdentifier)
  , so.principal_id
  , so.schema_id
  , so.parent_object_id
  , type                      = so.type Collate Database_Default
  , so.type_desc
  , so.create_date
  , so.modify_date
  , so.is_ms_shipped
  , so.is_published
  , so.is_schema_published
  , st.temporal_type
  , st.temporal_type_desc
  , st.history_table_id
  , st.max_column_id_used
  , sql_modules_definition    = sm.definition
From
    sys_dwh.objects                 As so
    Left Outer Join
        sys_dwh.schemas             As sch
            On
            sch.schema_id        = so.schema_id

    Left Outer Join
        repo_sys.ExtendedProperties As ep
            On
            ep.major_id          = so.object_id
            And ep.minor_id      = 0
            And ep.property_name = N'RepoObject_guid'

    Left Outer Join
        sys_dwh.tables              As st
            On
            st.object_id         = so.object_id

    Left Outer Join
        sys_dwh.sql_modules         As sm
            On
            sm.object_id         = so.object_id
Where
    sch.name <> 'sys'