repo.usp_main - P

type: P ( stored procedure ), modify_date: 2022-09-05 18:23:35

RepoObject_guid: 6BCBC49C-3862-EB11-84DC-A81E8446D5B0

uspgenerator_usp_id: 2

Description

main procedure

this central procedure must be executed regularly, try to get e feeling, when it is required
It does:

  • EXEC repo.usp_sync_guid to synchronize repository database and dwh database

    • some dwh database extended properties (ep) are synchronized with repository database

      • ep RepoObject_guid for each database object

      • ep RepoObjectColumn_guid for each database object column

  • index processing

    • combination of real and virtual indexes

    • virtual and real foreign key

    • code generation and updates for persistence procedures

  • process references and data lineage

  • inheritance of properties

see Procedure steps for details.

use links in Referenced objects to get details of called sub procedures

Examples

Example 1. Usage
EXEC [repo].[usp_main]

Parameters

  • @execution_instance_guid (uniqueidentifier)

  • @ssis_execution_id (bigint)

  • @sub_execution_id (int)

  • @parent_execution_log_id (bigint)

Procedure steps

uspgenerator_usp_id: 2

Table 1. Steps in [repo].[usp_main]
Number Name (Action, Source, Target) Parent

150

EXEC [config].[usp_init_parameter]

Statement
EXEC [config].[usp_init_parameter]

210

(select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 1

  • IF (select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 1

Statement
(select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 1

211

[repo].[usp_sync_guid]

  • EXEC [repo].[usp_sync_guid]

Statement
[repo].[usp_sync_guid]

210

220

(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1

  • IF (select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1

Statement
(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1

221

[repo].[usp_sync_guid_ssas]

  • EXEC [repo].[usp_sync_guid_ssas]

Statement
[repo].[usp_sync_guid_ssas]

220

230

*(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1 AND ( 'sync enable', 'dwh' )) = 0 *

  • IF (select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1 AND (select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 0

Statement
(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1
AND
(select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 0

231

delete where is_ssas = 0 and is_external = 0

  • d

  • [repo].[RepoObject]

  • [repo].[RepoObject]

Statement
Delete From
repo.RepoObject
Where
    is_ssas         = 0
    And is_external = 0

230

235

[ssas].[usp_PERSIST_RepoObjectColumn_translation_T]

  • EXEC [ssas].[usp_PERSIST_RepoObjectColumn_translation_T]

it is required in [docs].[RepoObject_ColumnList]

Statement
[ssas].[usp_PERSIST_RepoObjectColumn_translation_T]

240

[reference].[usp_additional_Reference]

  • EXEC [reference].[usp_additional_Reference]

Statement
[reference].[usp_additional_Reference]

250

[reference].[usp_PERSIST_RepoObject_reference_T]

  • EXEC [reference].[usp_PERSIST_RepoObject_reference_T]

Statement
[reference].[usp_PERSIST_RepoObject_reference_T]

261

*[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget from [reference].[RepoObject_reference_persistence] *

  • [reference].[RepoObject_reference_persistence]

  • [reference].[RepoObject_reference_T]

Statement
Update
    T1
Set
    referenced_is_PersistenceSource = 1
  , referencing_is_PersistenceTarget = 1
From
    reference.RepoObject_reference_T               As T1
    Inner Join
        reference.RepoObject_reference_persistence As T2
            On
            T1.referenced_RepoObject_guid      = T2.referenced_RepoObject_guid
            And T1.referencing_RepoObject_guid = T2.referencing_RepoObject_guid;

262

[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget, is_ReversePersistenceViaView from [reference].[RepoObject_reference_persistence_target_as_source]

  • [reference].[RepoObject_reference_persistence_target_as_source]

  • [reference].[RepoObject_reference_T]

Statement
Update
    T1
Set
    referenced_is_PersistenceSource = 1
  , referencing_is_PersistenceTarget = 1
  , is_ReversePersistenceViaView = 1
From
    reference.RepoObject_reference_T                                    As T1
    Inner Join
        [reference].[RepoObject_reference_persistence_target_as_source] As T2
            On
            T1.referenced_RepoObject_guid      = T2.referenced_RepoObject_guid
            And T1.referencing_RepoObject_guid = T2.referencing_RepoObject_guid;

263

[reference].[RepoObject_reference_T] set referenced_is_PersistenceTarget, referencing_is_PersistenceUspTargetRef

  • [repo].[RepoObject]

  • [reference].[RepoObject_reference_T]

Statement
Update
    T1
Set
    referenced_is_PersistenceTarget = 1
  , referencing_is_PersistenceUspTargetRef = 1
From
    reference.RepoObject_reference_T As T1
    Inner Join
        repo.RepoObject              As T2
            On
            T1.referenced_RepoObject_guid = T2.RepoObject_guid
            And T1.referencing_fullname2  = T2.usp_persistence_fullname2;

280

[reference].[usp_RepoObject_ReferenceTree_insert]

  • EXEC [reference].[usp_RepoObject_ReferenceTree_insert]

Statement
[reference].[usp_RepoObject_ReferenceTree_insert]

290

[reference].[usp_PERSIST_RepoObjectColumn_reference_T]

  • EXEC [reference].[usp_PERSIST_RepoObjectColumn_reference_T]

Statement
[reference].[usp_PERSIST_RepoObjectColumn_reference_T]

292

[repo].[usp_PERSIST_RepoObject_sat2_T]

  • EXEC [repo].[usp_PERSIST_RepoObject_sat2_T]

Statement
[repo].[usp_PERSIST_RepoObject_sat2_T]

300

(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_FirstResultSet', DEFAULT)) = 1

  • IF (select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_FirstResultSet', DEFAULT)) = 1

Statement
(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_FirstResultSet', DEFAULT)) = 1

310

[reference].[usp_RepoObjectSource_FirstResultSet]

  • EXEC [reference].[usp_RepoObjectSource_FirstResultSet] --This can take a very long time

Statement
[reference].[usp_RepoObjectSource_FirstResultSet]
--This can take a very long time

300

400

(select [config].[fs_get_parameter_value]('main enable usp_RepoObject_update_SysObjectQueryPlan', DEFAULT)) = 1

  • IF (select [config].[fs_get_parameter_value]('main enable usp_RepoObject_update_SysObjectQueryPlan', DEFAULT)) = 1

Statement
(select [config].[fs_get_parameter_value]('main enable usp_RepoObject_update_SysObjectQueryPlan', DEFAULT)) = 1

410

[reference].[usp_RepoObject_update_SysObjectQueryPlan]

  • EXEC [reference].[usp_RepoObject_update_SysObjectQueryPlan]

This can take a very long time

Statement
[reference].[usp_RepoObject_update_SysObjectQueryPlan]

400

500

(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_QueryPlan', DEFAULT)) = 1

  • IF (select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_QueryPlan', DEFAULT)) = 1

Statement
(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_QueryPlan', DEFAULT)) = 1

510

[reference].[usp_RepoObjectSource_QueryPlan] --This can take a very long time

  • EXEC [reference].[usp_RepoObjectSource_QueryPlan]

This can take a very long time

Statement
[reference].[usp_RepoObjectSource_QueryPlan]

500

610

[reference].[usp_update_Referencing_Count]

  • EXEC [reference].[usp_update_Referencing_Count]

Statement
[reference].[usp_update_Referencing_Count]

710

[repo].[usp_index_inheritance]

  • EXEC [repo].[usp_index_inheritance]

todo:

should or could be executed several times until no new indexes are inherited

Statement
[repo].[usp_index_inheritance]

720

[repo].[usp_Index_ForeignKey]

  • EXEC [repo].[usp_Index_ForeignKey]

Statement
[repo].[usp_Index_ForeignKey]

730

[property].[usp_PERSIST_PropertyName_Measure_T]

  • EXEC [property].[usp_PERSIST_PropertyName_Measure_T]

Statement
[property].[usp_PERSIST_PropertyName_Measure_T]

810

[repo].[usp_RepoObjectColumn_update_RepoObjectColumn_column_id]

  • EXEC [repo].[usp_RepoObjectColumn_update_RepoObjectColumn_column_id]

This must happen later than the index logic, because the PK can change there. And this affects the order of the columns.

Statement
[repo].[usp_RepoObjectColumn_update_RepoObjectColumn_column_id]

910

[repo].[usp_GeneratorUsp_insert_update_persistence]

  • EXEC [uspgenerator].[usp_GeneratorUsp_insert_update_persistence]

RepoObjectColumn_column_id is required and should be updated before

Statement
[uspgenerator].[usp_GeneratorUsp_insert_update_persistence]

920

Persistence Target: update repo.RepoObject set InheritanceType = 13 (if NULL)

  • u

  • [repo].[RepoObject_persistence]

  • [repo].[RepoObject]

Statement
Update
    repo.RepoObject
Set
    InheritanceType = 13
Where
    ( RepoObject_type = 'U' )
    And ( InheritanceType Is Null )
    And Exists
(
    Select
        1
    From
        repo.RepoObject_persistence As rop
    Where
        rop.target_RepoObject_guid = repo.RepoObject.RepoObject_guid
)

3200

[property].[usp_RepoObjectProperty_collect]

  • EXEC [property].[usp_RepoObjectProperty_collect]

Statement
[property].[usp_RepoObjectProperty_collect]

3310

[reference].[usp_PERSIST_additional_Reference_from_properties_tgt]

  • EXEC [reference].[usp_PERSIST_additional_Reference_from_properties_tgt]

Statement
[reference].[usp_PERSIST_additional_Reference_from_properties_tgt]

3320

[reference].[usp_PERSIST_additional_Reference_wo_columns_from_properties_tgt]

  • EXEC [reference].[usp_PERSIST_additional_Reference_wo_columns_from_properties_tgt]

Statement
[reference].[usp_PERSIST_additional_Reference_wo_columns_from_properties_tgt]

3330

[reference].[usp_PERSIST_additional_Reference_from_ssas_tgt]

  • EXEC [reference].[usp_PERSIST_additional_Reference_from_ssas_tgt]

Statement
[reference].[usp_PERSIST_additional_Reference_from_ssas_tgt]

3400

[property].[usp_external_property_import]

  • EXEC [property].[usp_external_property_import]

Statement
[property].[usp_external_property_import]

4110

[property].[usp_RepoObject_Inheritance]

  • EXEC [property].[usp_RepoObject_Inheritance]

Statement
[property].[usp_RepoObject_Inheritance]

4120

[property].[usp_RepoObjectColumn_Inheritance]

  • EXEC [property].[usp_RepoObjectColumn_Inheritance]

Statement
[property].[usp_RepoObjectColumn_Inheritance]

Entity Diagram

entity-repo.usp_main

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.usp_main

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.usp_main

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.usp_main

sql_modules_definition

repo.usp_main - P script
/*
code of this procedure is managed in the dhw repository. Do not modify manually.
Use [uspgenerator].[GeneratorUsp], [uspgenerator].[GeneratorUspParameter], [uspgenerator].[GeneratorUspStep], [uspgenerator].[GeneratorUsp_SqlUsp]
*/
CREATE   PROCEDURE [repo].[usp_main]
----keep the code between logging parameters and "START" unchanged!
---- parameters, used for logging; you don't need to care about them, but you can use them, wenn calling from SSIS or in your workflow to log the context of the procedure call
  @execution_instance_guid UNIQUEIDENTIFIER = NULL --SSIS system variable ExecutionInstanceGUID could be used, any other unique guid is also fine. If NULL, then NEWID() is used to create one
, @ssis_execution_id BIGINT = NULL --only SSIS system variable ServerExecutionID should be used, or any other consistent number system, do not mix different number systems
, @sub_execution_id INT = NULL --in case you log some sub_executions, for example in SSIS loops or sub packages
, @parent_execution_log_id BIGINT = NULL --in case a sup procedure is called, the @current_execution_log_id of the parent procedure should be propagated here. It allowes call stack analyzing

AS
BEGIN
DECLARE
 --
   @current_execution_log_id BIGINT --this variable should be filled only once per procedure call, it contains the first logging call for the step 'start'.
 , @current_execution_guid UNIQUEIDENTIFIER = NEWID() --a unique guid for any procedure call. It should be propagated to sub procedures using "@parent_execution_log_id = @current_execution_log_id"
 , @source_object NVARCHAR(261) = NULL --use it like '[schema].[object]', this allows data flow vizualizatiuon (include square brackets)
 , @target_object NVARCHAR(261) = NULL --use it like '[schema].[object]', this allows data flow vizualizatiuon (include square brackets)
 , @proc_id INT = @@procid
 , @proc_schema_name NVARCHAR(128) = OBJECT_SCHEMA_NAME(@@procid) --schema ande name of the current procedure should be automatically logged
 , @proc_name NVARCHAR(128) = OBJECT_NAME(@@procid)               --schema ande name of the current procedure should be automatically logged
 , @event_info NVARCHAR(MAX)
 , @step_id INT = 0
 , @step_name NVARCHAR(1000) = NULL
 , @rows INT

--[event_info] get's only the information about the "outer" calling process
--wenn the procedure calls sub procedures, the [event_info] will not change
SET @event_info = (
  SELECT TOP 1 [event_info]
  FROM sys.dm_exec_input_buffer(@@spid, CURRENT_REQUEST_ID())
  ORDER BY [event_info]
  )

IF @execution_instance_guid IS NULL
 SET @execution_instance_guid = NEWID();
--
--SET @rows = @@ROWCOUNT;
SET @step_id = @step_id + 1
SET @step_name = 'start'
SET @source_object = NULL
SET @target_object = NULL

EXEC logs.usp_ExecutionLog_insert
 --these parameters should be the same for all logging execution
   @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 --the following parameters are individual for each call
 , @step_id = @step_id --@step_id should be incremented before each call
 , @step_name = @step_name --assign individual step names for each call
 --only the "start" step should return the log id into @current_execution_log_id
 --all other calls should not overwrite @current_execution_log_id
 , @execution_log_id = @current_execution_log_id OUTPUT
----you can log the content of your own parameters, do this only in the start-step
----data type is sql_variant

--
PRINT '[repo].[usp_main]'
--keep the code between logging parameters and "START" unchanged!
--
----START
--
----- start here with your own code
--
/*{"ReportUspStep":[{"Number":150,"Name":"EXEC [config].[usp_init_parameter]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":0}]}*/
PRINT CONCAT('usp_id;Number;Parent_Number: ',2,';',150,';',NULL);

EXEC [config].[usp_init_parameter]

-- Logging START --
SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = 'EXEC [config].[usp_init_parameter]'
SET @source_object = NULL
SET @target_object = NULL

EXEC logs.usp_ExecutionLog_insert
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object

-- Logging END --

/*{"ReportUspStep":[{"Number":210,"Name":"(select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 1","has_logging":0,"is_condition":1,"is_inactive":0,"is_SubProcedure":0}]}*/
IF (select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 1

/*{"ReportUspStep":[{"Number":211,"Parent_Number":210,"Name":"[repo].[usp_sync_guid]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
BEGIN
EXEC [repo].[usp_sync_guid]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id

END;

/*{"ReportUspStep":[{"Number":220,"Name":"(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1","has_logging":0,"is_condition":1,"is_inactive":0,"is_SubProcedure":0}]}*/
IF (select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1

/*{"ReportUspStep":[{"Number":221,"Parent_Number":220,"Name":"[repo].[usp_sync_guid_ssas]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
BEGIN
EXEC [repo].[usp_sync_guid_ssas]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id

END;

/*{"ReportUspStep":[{"Number":230,"Name":"(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1 AND ( 'sync enable', 'dwh' )) = 0 ","has_logging":0,"is_condition":1,"is_inactive":0,"is_SubProcedure":0}]}*/
IF (select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1
AND
(select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 0

/*{"ReportUspStep":[{"Number":231,"Parent_Number":230,"Name":"delete where is_ssas = 0 and is_external = 0","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":0,"log_source_object":"[repo].[RepoObject]","log_target_object":"[repo].[RepoObject]","log_flag_InsertUpdateDelete":"d"}]}*/
BEGIN
PRINT CONCAT('usp_id;Number;Parent_Number: ',2,';',231,';',230);

Delete From
repo.RepoObject
Where
    is_ssas         = 0
    And is_external = 0

-- Logging START --
SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = 'delete where is_ssas = 0 and is_external = 0'
SET @source_object = '[repo].[RepoObject]'
SET @target_object = '[repo].[RepoObject]'

EXEC logs.usp_ExecutionLog_insert
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object
 , @deleted = @rows
-- Logging END --
END;

/*{"ReportUspStep":[{"Number":235,"Name":"[ssas].[usp_PERSIST_RepoObjectColumn_translation_T]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [ssas].[usp_PERSIST_RepoObjectColumn_translation_T]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":240,"Name":"[reference].[usp_additional_Reference]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_additional_Reference]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":250,"Name":"[reference].[usp_PERSIST_RepoObject_reference_T]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_PERSIST_RepoObject_reference_T]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":261,"Name":"[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget from [reference].[RepoObject_reference_persistence] ","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":0,"log_source_object":"[reference].[RepoObject_reference_persistence]","log_target_object":"[reference].[RepoObject_reference_T]"}]}*/
PRINT CONCAT('usp_id;Number;Parent_Number: ',2,';',261,';',NULL);

Update
    T1
Set
    referenced_is_PersistenceSource = 1
  , referencing_is_PersistenceTarget = 1
From
    reference.RepoObject_reference_T               As T1
    Inner Join
        reference.RepoObject_reference_persistence As T2
            On
            T1.referenced_RepoObject_guid      = T2.referenced_RepoObject_guid
            And T1.referencing_RepoObject_guid = T2.referencing_RepoObject_guid;

-- Logging START --
SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = '[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget from [reference].[RepoObject_reference_persistence] '
SET @source_object = '[reference].[RepoObject_reference_persistence]'
SET @target_object = '[reference].[RepoObject_reference_T]'

EXEC logs.usp_ExecutionLog_insert
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object

-- Logging END --

/*{"ReportUspStep":[{"Number":262,"Name":"[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget, is_ReversePersistenceViaView from [reference].[RepoObject_reference_persistence_target_as_source]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":0,"log_source_object":"[reference].[RepoObject_reference_persistence_target_as_source]","log_target_object":"[reference].[RepoObject_reference_T]"}]}*/
PRINT CONCAT('usp_id;Number;Parent_Number: ',2,';',262,';',NULL);

Update
    T1
Set
    referenced_is_PersistenceSource = 1
  , referencing_is_PersistenceTarget = 1
  , is_ReversePersistenceViaView = 1
From
    reference.RepoObject_reference_T                                    As T1
    Inner Join
        [reference].[RepoObject_reference_persistence_target_as_source] As T2
            On
            T1.referenced_RepoObject_guid      = T2.referenced_RepoObject_guid
            And T1.referencing_RepoObject_guid = T2.referencing_RepoObject_guid;

-- Logging START --
SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = '[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget, is_ReversePersistenceViaView from [reference].[RepoObject_reference_persistence_target_as_source]'
SET @source_object = '[reference].[RepoObject_reference_persistence_target_as_source]'
SET @target_object = '[reference].[RepoObject_reference_T]'

EXEC logs.usp_ExecutionLog_insert
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object

-- Logging END --

/*{"ReportUspStep":[{"Number":263,"Name":"[reference].[RepoObject_reference_T] set referenced_is_PersistenceTarget, referencing_is_PersistenceUspTargetRef","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":0,"log_source_object":"[repo].[RepoObject]","log_target_object":"[reference].[RepoObject_reference_T]"}]}*/
PRINT CONCAT('usp_id;Number;Parent_Number: ',2,';',263,';',NULL);

Update
    T1
Set
    referenced_is_PersistenceTarget = 1
  , referencing_is_PersistenceUspTargetRef = 1
From
    reference.RepoObject_reference_T As T1
    Inner Join
        repo.RepoObject              As T2
            On
            T1.referenced_RepoObject_guid = T2.RepoObject_guid
            And T1.referencing_fullname2  = T2.usp_persistence_fullname2;

-- Logging START --
SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = '[reference].[RepoObject_reference_T] set referenced_is_PersistenceTarget, referencing_is_PersistenceUspTargetRef'
SET @source_object = '[repo].[RepoObject]'
SET @target_object = '[reference].[RepoObject_reference_T]'

EXEC logs.usp_ExecutionLog_insert
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object

-- Logging END --

/*{"ReportUspStep":[{"Number":280,"Name":"[reference].[usp_RepoObject_ReferenceTree_insert]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_RepoObject_ReferenceTree_insert]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":290,"Name":"[reference].[usp_PERSIST_RepoObjectColumn_reference_T]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_PERSIST_RepoObjectColumn_reference_T]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":292,"Name":"[repo].[usp_PERSIST_RepoObject_sat2_T]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [repo].[usp_PERSIST_RepoObject_sat2_T]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":300,"Name":"(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_FirstResultSet', DEFAULT)) = 1","has_logging":0,"is_condition":1,"is_inactive":0,"is_SubProcedure":0}]}*/
IF (select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_FirstResultSet', DEFAULT)) = 1

/*{"ReportUspStep":[{"Number":310,"Parent_Number":300,"Name":"[reference].[usp_RepoObjectSource_FirstResultSet]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
BEGIN
EXEC [reference].[usp_RepoObjectSource_FirstResultSet]
--This can take a very long time
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id

END;

/*{"ReportUspStep":[{"Number":400,"Name":"(select [config].[fs_get_parameter_value]('main enable usp_RepoObject_update_SysObjectQueryPlan', DEFAULT)) = 1","has_logging":0,"is_condition":1,"is_inactive":0,"is_SubProcedure":0}]}*/
IF (select [config].[fs_get_parameter_value]('main enable usp_RepoObject_update_SysObjectQueryPlan', DEFAULT)) = 1

/*{"ReportUspStep":[{"Number":410,"Parent_Number":400,"Name":"[reference].[usp_RepoObject_update_SysObjectQueryPlan]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
BEGIN
EXEC [reference].[usp_RepoObject_update_SysObjectQueryPlan]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id

END;

/*{"ReportUspStep":[{"Number":500,"Name":"(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_QueryPlan', DEFAULT)) = 1","has_logging":0,"is_condition":1,"is_inactive":0,"is_SubProcedure":0}]}*/
IF (select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_QueryPlan', DEFAULT)) = 1

/*{"ReportUspStep":[{"Number":510,"Parent_Number":500,"Name":"[reference].[usp_RepoObjectSource_QueryPlan]\r\n--This can take a very long time","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
BEGIN
EXEC [reference].[usp_RepoObjectSource_QueryPlan]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id

END;

/*{"ReportUspStep":[{"Number":610,"Name":"[reference].[usp_update_Referencing_Count]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_update_Referencing_Count]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":710,"Name":"[repo].[usp_index_inheritance]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [repo].[usp_index_inheritance]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":720,"Name":"[repo].[usp_Index_ForeignKey]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [repo].[usp_Index_ForeignKey]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":730,"Name":"[property].[usp_PERSIST_PropertyName_Measure_T]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [property].[usp_PERSIST_PropertyName_Measure_T]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":810,"Name":"[repo].[usp_RepoObjectColumn_update_RepoObjectColumn_column_id]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [repo].[usp_RepoObjectColumn_update_RepoObjectColumn_column_id]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":910,"Name":"[repo].[usp_GeneratorUsp_insert_update_persistence]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [uspgenerator].[usp_GeneratorUsp_insert_update_persistence]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":920,"Name":"Persistence Target: update repo.RepoObject set InheritanceType = 13 (if NULL)","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":0,"log_source_object":"[repo].[RepoObject_persistence]","log_target_object":"[repo].[RepoObject]","log_flag_InsertUpdateDelete":"u"}]}*/
PRINT CONCAT('usp_id;Number;Parent_Number: ',2,';',920,';',NULL);

Update
    repo.RepoObject
Set
    InheritanceType = 13
Where
    ( RepoObject_type = 'U' )
    And ( InheritanceType Is Null )
    And Exists
(
    Select
        1
    From
        repo.RepoObject_persistence As rop
    Where
        rop.target_RepoObject_guid = repo.RepoObject.RepoObject_guid
)

-- Logging START --
SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = 'Persistence Target: update repo.RepoObject set InheritanceType = 13 (if NULL)'
SET @source_object = '[repo].[RepoObject_persistence]'
SET @target_object = '[repo].[RepoObject]'

EXEC logs.usp_ExecutionLog_insert
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object
 , @updated = @rows
-- Logging END --

/*{"ReportUspStep":[{"Number":3200,"Name":"[property].[usp_RepoObjectProperty_collect]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [property].[usp_RepoObjectProperty_collect]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":3310,"Name":"[reference].[usp_PERSIST_additional_Reference_from_properties_tgt]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_PERSIST_additional_Reference_from_properties_tgt]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":3320,"Name":"[reference].[usp_PERSIST_additional_Reference_wo_columns_from_properties_tgt]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_PERSIST_additional_Reference_wo_columns_from_properties_tgt]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":3330,"Name":"[reference].[usp_PERSIST_additional_Reference_from_ssas_tgt]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [reference].[usp_PERSIST_additional_Reference_from_ssas_tgt]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":3400,"Name":"[property].[usp_external_property_import]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [property].[usp_external_property_import]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":4110,"Name":"[property].[usp_RepoObject_Inheritance]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [property].[usp_RepoObject_Inheritance]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


/*{"ReportUspStep":[{"Number":4120,"Name":"[property].[usp_RepoObjectColumn_Inheritance]","has_logging":1,"is_condition":0,"is_inactive":0,"is_SubProcedure":1}]}*/
EXEC [property].[usp_RepoObjectColumn_Inheritance]
--add your own parameters
--logging parameters
 @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @current_execution_log_id


--
--finish your own code here
--keep the code between "END" and the end of the procedure unchanged!
--
--END
--
--SET @rows = @@ROWCOUNT
SET @step_id = @step_id + 1
SET @step_name = 'end'
SET @source_object = NULL
SET @target_object = NULL

EXEC logs.usp_ExecutionLog_insert
   @execution_instance_guid = @execution_instance_guid
 , @ssis_execution_id = @ssis_execution_id
 , @sub_execution_id = @sub_execution_id
 , @parent_execution_log_id = @parent_execution_log_id
 , @current_execution_guid = @current_execution_guid
 , @proc_id = @proc_id
 , @proc_schema_name = @proc_schema_name
 , @proc_name = @proc_name
 , @event_info = @event_info
 , @step_id = @step_id
 , @step_name = @step_name
 , @source_object = @source_object
 , @target_object = @target_object

END