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.
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_GeneratorUsp_check_persistence__1
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_GeneratorUsp_check_persistence__3
-
IndexSemanticGroup: no_group
-
[column-usp_schema]; nvarchar(128)
-
[column-usp_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_GeneratorUsp_check_persistence__4
-
IndexSemanticGroup: no_group
-
[column-target_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
is_persistence_check_duplicate_per_pk
is_persistence_check_duplicate_per_pk |
|
|
is_persistence_check_for_empty_source
is_persistence_check_for_empty_source |
|
|
RepoObject_fullname
RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
temporal_type
temporal_type |
|
|
(CONVERT([tinyint],case [has_history] when (1) then (2) else (0) end))
usp_fullname
usp_fullname |
|
|
(concat('[',[usp_schema],'].[',[usp_name],']'))
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_' )