uspgenerator.GeneratorUsp_SqlViewPersistenceUpdateCheck - V

type: V ( view ), modify_date: 2022-09-07 10:18:56

RepoObject_guid: D7539585-D92A-ED11-8577-A81E8446D5B0

Description

Examples

Entity Diagram

entity-uspgenerator.generatorusp_sqlviewpersistenceupdatecheck

Columns

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

nvarchar(max)

NOT NULL

nvarchar(261)

NOT NULL

int

NOT NULL

nvarchar(517)

NULL

nvarchar(280)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-uspgenerator.generatorusp_sqlviewpersistenceupdatecheck

References

Object Reference Diagram - 1 1

entity_1_1_objectref-uspgenerator.generatorusp_sqlviewpersistenceupdatecheck

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-uspgenerator.generatorusp_sqlviewpersistenceupdatecheck

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-uspgenerator.generatorusp_sqlviewpersistenceupdatecheck

Column Reference Diagram

entity_1_1_colref-uspgenerator.generatorusp_sqlviewpersistenceupdatecheck

Indexes

idx_GeneratorUsp_SqlViewPersistenceUpdateCheck__1

Column Details

_

SqlViewPersistenceUpdateCheck

SqlViewPersistenceUpdateCheck

nvarchar(max)

NOT NULL

usp_fullname

usp_fullname

nvarchar(261)

NOT NULL

Description

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


usp_id

usp_id

int

NOT NULL

Referenced Columns

view_fullname

view_fullname

nvarchar(517)

NULL

view_fullname2

view_fullname2

nvarchar(280)

NOT NULL

sql_modules_definition

uspgenerator.GeneratorUsp_SqlViewPersistenceUpdateCheck - V script
--similair logic to [uspgenerator].[GeneratorUspStep_Persistence_src]
--step 600
--'update changed'

CREATE View uspgenerator.GeneratorUsp_SqlViewPersistenceUpdateCheck
As
Select
    usp_id                        = gu.id
  --, view_fullname                 = Replace ( gu.usp_fullname, 'usp_PERSIST_', 'PersistenceUpdateCheck_' )
  , view_fullname                 = QuoteName ( ro.RepoObject_schema_name ) + '.'
                                    + QuoteName ( 'PersistenceUpdateCheck_' + ro.RepoObject_name )
  , view_fullname2                = ro.RepoObject_schema_name + '.' + 'PersistenceUpdateCheck_' + ro.RepoObject_name
  , SqlViewPersistenceUpdateCheck = Concat (
                                               'USE  ['
                                             , dwhdb.dwh_database_name
                                             , ']'
                                             , Char ( 13 ) + Char ( 10 )
                                             , 'GO'
                                             , Char ( 13 ) + Char ( 10 )
                                             , '/*'
                                             , Char ( 13 ) + Char ( 10 )
                                             , 'code of this view is generated in the dhw repository. Do not modify manually.'
                                             , Char ( 13 ) + Char ( 10 )
                                             , 'Use [uspgenerator].[GeneratorUsp] to define persistence'
                                             , Char ( 13 ) + Char ( 10 )
                                             , '[uspgenerator].[GeneratorUsp_SqlViewPersistenceUpdateCheck] to get the code of the view'
                                             , Char ( 13 ) + Char ( 10 )
                                             , '[uspgenerator].[GeneratorUsp_SqlUsp] to get the code of the usp and also the code of the view'
                                             , Char ( 13 ) + Char ( 10 )
                                             , '*/'
                                             , Char ( 13 ) + Char ( 10 )
                                             , 'CREATE OR ALTER VIEW '
                                             , Char ( 13 ) + Char ( 10 )
                                             --, Replace ( gu.usp_fullname, 'usp_PERSIST_', 'PersistenceUpdateCheck_' )
                                             , QuoteName ( ro.RepoObject_schema_name ) + '.'
                                               + QuoteName ( 'PersistenceUpdateCheck_' + ro.RepoObject_name )
                                             , '
AS
SELECT
'
                                             , ro.PersistenceSrcTgtColumnList
                                             --2022-09-07 #70 uspgenerator.GeneratorUsp_SqlViewPersistenceUpdateCheck - add some metadata as columns (Schema, target table)
                                             , Char ( 13 ) + Char ( 10 )
                                             , ', src_fullname = ''' + ro.persistence_source_RepoObject_fullname + ''''
                                             , Char ( 13 ) + Char ( 10 )
                                             , ', src_fullname2 = ''' + ro.persistence_source_RepoObject_fullname2
                                               + ''''
                                             --, Char ( 13 ) + Char ( 10 )
                                             --, ', target_fullname = ''' + Replace ( gu.usp_fullname, 'usp_PERSIST_', '' )
                                             --  + ''''
                                             , Char ( 13 ) + Char ( 10 )
                                             , ', tgt_SchemaName = ''' + ro.RepoObject_schema_name + ''''
                                             , Char ( 13 ) + Char ( 10 )
                                             , ', tgt_Name = ''' + ro.RepoObject_name + ''''
                                             , Char ( 13 ) + Char ( 10 )
                                             , ', tgt_Fullname = ''' + ro.RepoObject_fullname + ''''
                                             , Char ( 13 ) + Char ( 10 )
                                             , ', tgt_Fullname2 = ''' + ro.RepoObject_fullname2 + ''''
                                             , Char ( 13 ) + Char ( 10 )
                                             , Concat ( ', uspgenerator_usp_id = ', gu.id )
                                             , Char ( 13 ) + Char ( 10 )
                                             , ', uspgenerator_usp_Fullname = ''' + gu.usp_fullname + ''''
                                             , Char ( 13 ) + Char ( 10 )
                                             , '
FROM ' +                                     ro.RepoObject_fullname + ' AS T
INNER JOIN ' +                               ro.persistence_source_SysObject_fullname_or_tempsource + ' AS S
ON
' +                                          i.PersistenceWhereColumnList
                                             --ro.PersistenceCompareColumnList could be empty
                                             , '
WHERE
' +                                          ro.PersistenceCompareColumnList
                                             , '
GO

'
                                           )
  , gu.usp_fullname
From
    repo.RepoObject_gross2                As ro
    Inner Join
        uspgenerator.GeneratorUsp         As gu
            On
            ro.RepoObject_schema_name = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
    --INNER JOIN because step 600 should be created only when PK exists in persistence_source

    Inner Join
        repo.RepoObject_gross             As ro_s
            On
            ro_s.RepoObject_guid = ro.persistence_source_RepoObject_guid

    Inner Join
        repo.Index_ColumList_T            As i
            On
            i.index_guid = ro_s.pk_index_guid
    Cross Join config.ftv_dwh_database () As dwhdb