uspgenerator.GeneratorUsp_check_persistence - V

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

RepoObject_guid: F604F585-2055-EC11-8533-A81E8446D5B0

Description

Sometimes it can happen that a persistence was created with the Persistence Generator and still exists in uspgenerator.GeneratorUsp - U, although the persistence has already been removed from repo.RepoObject_persistence - U.

This view helps to identify such entries and then remove such legacy entries from uspgenerator.GeneratorUsp - U

If the last columns of the uspgenerator.GeneratorUsp_check_persistence - V view have the value NULL, then there is no persistence anymore. The corresponding entries can then be removed from uspgenerator.GeneratorUsp - U after careful checking.

Examples

Entity Diagram

entity-uspgenerator.generatorusp_check_persistence

Columns

Table 1. Columns of uspgenerator.GeneratorUsp_check_persistence - V
PK Column Name Data Type NULL? ID

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

bit

NULL

bit

NULL

tinyint

NOT NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

id

int

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(261)

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

uniqueidentifier

NULL

tinyint

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(261)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-uspgenerator.generatorusp_check_persistence

References

Object Reference Diagram - 1 1

entity_1_1_objectref-uspgenerator.generatorusp_check_persistence

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-uspgenerator.generatorusp_check_persistence

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-uspgenerator.generatorusp_check_persistence

Column Reference Diagram

entity_1_1_colref-uspgenerator.generatorusp_check_persistence

Indexes

idx_GeneratorUsp_check_persistence__1

idx_GeneratorUsp_check_persistence__2

  • IndexSemanticGroup: no_group

  • PK, Unique, Real: 0, 0, 0

idx_GeneratorUsp_check_persistence__3

idx_GeneratorUsp_check_persistence__4

Column Details

_

ColumnListIgnore

ColumnListIgnore

nvarchar(4000)

NULL

ColumnListNoCompareButUpdate

ColumnListNoCompareButUpdate

nvarchar(4000)

NULL

ColumnListNoCompareNoUpdate

ColumnListNoCompareNoUpdate

nvarchar(4000)

NULL

has_history

has_history

bit

NULL

has_history_columns

has_history_columns

bit

NULL

has_logging

has_logging

tinyint

NOT NULL

history_schema_name

history_schema_name

nvarchar(128)

NULL

history_table_name

history_table_name

nvarchar(128)

NULL

id

id

int

NOT NULL

Referenced Columns

is_persistence

is_persistence

bit

NULL

is_persistence_check_duplicate_per_pk

is_persistence_check_duplicate_per_pk

bit

NULL

is_persistence_check_for_empty_source

is_persistence_check_for_empty_source

bit

NULL

is_persistence_delete_changed

is_persistence_delete_changed

bit

NULL

is_persistence_delete_missing

is_persistence_delete_missing

bit

NULL

is_persistence_insert

is_persistence_insert

bit

NULL

is_persistence_persist_source

is_persistence_persist_source

bit

NULL

is_persistence_truncate

is_persistence_truncate

bit

NULL

is_persistence_update_changed

is_persistence_update_changed

bit

NULL

postscript

postscript

nvarchar(max)

NULL

prescript

prescript

nvarchar(max)

NULL

RepoObject_fullname

RepoObject_fullname

nvarchar(261)

NULL

Description

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


RepoObject_guid

RepoObject_guid

uniqueidentifier

NULL

Referenced Columns

source_RepoObject_guid

source_RepoObject_guid

uniqueidentifier

NULL

source_RepoObject_name

source_RepoObject_name

nvarchar(128)

NULL

target_RepoObject_guid

target_RepoObject_guid

uniqueidentifier

NULL

temporal_type

temporal_type

tinyint

NULL

Description

(CONVERT([tinyint],case [has_history] when (1) then (2) else (0) end))


usp_Description

usp_Description

nvarchar(4000)

NULL

usp_Examples

usp_Examples

nvarchar(4000)

NULL

usp_fullname

usp_fullname

nvarchar(261)

NOT NULL

Description

(concat('[',[usp_schema],'].[',[usp_name],']'))


usp_name

usp_name

nvarchar(128)

NOT NULL

usp_schema

usp_schema

nvarchar(128)

NOT NULL

sql_modules_definition

uspgenerator.GeneratorUsp_check_persistence - V script
/*
<<property_start>>Description
Sometimes it can happen that a persistence was created with the Persistence Generator and still exists in xref:sqldb:uspgenerator.generatorusp.adoc[],
although the persistence has already been removed from xref:sqldb:repo.repoobject_persistence.adoc[].

This view helps to identify such entries and then remove such legacy entries from xref:sqldb:uspgenerator.generatorusp.adoc[]

If the last columns of the xref:sqldb:uspgenerator.generatorusp_check_persistence.adoc[] view have the value NULL, then there is no persistence anymore. The corresponding entries can then be removed from xref:sqldb:uspgenerator.generatorusp.adoc[] after careful checking.
<<property_end>>
*/
CREATE View uspgenerator.GeneratorUsp_check_persistence
As
Select
    T1.id
  , T1.usp_schema
  , T1.usp_name
  , T1.has_logging
  , T1.usp_Description
  , T1.usp_Examples
  , T1.usp_fullname
  , T2.RepoObject_guid
  , T2.RepoObject_fullname
  , T3.target_RepoObject_guid
  , T3.has_history
  , T3.has_history_columns
  , T3.history_schema_name
  , T3.history_table_name
  , T3.is_persistence_check_duplicate_per_pk
  , T3.is_persistence_check_for_empty_source
  , T3.is_persistence_delete_changed
  , T3.is_persistence_delete_missing
  , T3.is_persistence_insert
  , T3.is_persistence_truncate
  , T3.is_persistence_update_changed
  , T3.is_persistence_persist_source
  --, T3.is_persistence_merge_delete_missing
  --, T3.is_persistence_merge_insert
  --, T3.is_persistence_merge_update_changed
  , T3.[ColumnListNoCompareButUpdate]
  , T3.[ColumnListNoCompareNoUpdate]
  , T3.ColumnListIgnore
  , T3.prescript
  , T3.postscript
  , T3.source_RepoObject_guid
  , T3.source_RepoObject_name
  , T3.is_persistence
  , T3.temporal_type
From
    uspgenerator.GeneratorUsp       As T1
    Left Outer Join
        repo.RepoObject             As T2
            On
            T2.usp_persistence_fullname = T1.usp_fullname

    Left Outer Join
        repo.RepoObject_persistence As T3
            On
            T3.target_RepoObject_guid   = T2.RepoObject_guid
Where
    ( Left(T1.usp_name, 12) = 'usp_PERSIST_' )