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
Parameters
-
@execution_instance_guid (uniqueidentifier)
-
@ssis_execution_id (bigint)
-
@sub_execution_id (int)
-
@parent_execution_log_id (bigint)
Procedure steps
uspgenerator_usp_id: 2
Number | Name (Action, Source, Target) | Parent |
---|---|---|
150 |
EXEC [config].[usp_init_parameter] Statement
|
|
210 |
(select config.fs_get_parameter_value ( 'sync enable', 'dwh' )) = 1
Statement
|
|
211 |
[repo].[usp_sync_guid]
Statement
|
210 |
220 |
(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1
Statement
|
|
221 |
[repo].[usp_sync_guid_ssas]
Statement
|
220 |
230 |
*(select config.fs_get_parameter_value ( 'sync enable', 'ssas' )) = 1 AND ( 'sync enable', 'dwh' )) = 0 *
Statement
|
|
231 |
delete where is_ssas = 0 and is_external = 0
Statement
|
230 |
235 |
[ssas].[usp_PERSIST_RepoObjectColumn_translation_T]
it is required in [docs].[RepoObject_ColumnList] Statement
|
|
240 |
[reference].[usp_additional_Reference]
Statement
|
|
250 |
[reference].[usp_PERSIST_RepoObject_reference_T]
Statement
|
|
261 |
*[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget from [reference].[RepoObject_reference_persistence] *
Statement
|
|
262 |
[reference].[RepoObject_reference_T] set referenced_is_PersistenceSource, referencing_is_PersistenceTarget, is_ReversePersistenceViaView from [reference].[RepoObject_reference_persistence_target_as_source]
Statement
|
|
263 |
[reference].[RepoObject_reference_T] set referenced_is_PersistenceTarget, referencing_is_PersistenceUspTargetRef
Statement
|
|
280 |
[reference].[usp_RepoObject_ReferenceTree_insert]
Statement
|
|
290 |
[reference].[usp_PERSIST_RepoObjectColumn_reference_T]
Statement
|
|
292 |
[repo].[usp_PERSIST_RepoObject_sat2_T]
Statement
|
|
300 |
(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_FirstResultSet', DEFAULT)) = 1
Statement
|
|
310 |
[reference].[usp_RepoObjectSource_FirstResultSet]
Statement
|
300 |
400 |
(select [config].[fs_get_parameter_value]('main enable usp_RepoObject_update_SysObjectQueryPlan', DEFAULT)) = 1
Statement
|
|
410 |
[reference].[usp_RepoObject_update_SysObjectQueryPlan]
This can take a very long time Statement
|
400 |
500 |
(select [config].[fs_get_parameter_value]('main enable usp_RepoObjectSource_QueryPlan', DEFAULT)) = 1
Statement
|
|
510 |
[reference].[usp_RepoObjectSource_QueryPlan] --This can take a very long time
This can take a very long time Statement
|
500 |
610 |
[reference].[usp_update_Referencing_Count]
Statement
|
|
710 |
[repo].[usp_index_inheritance]
todo: should or could be executed several times until no new indexes are inherited Statement
|
|
720 |
[repo].[usp_Index_ForeignKey]
Statement
|
|
730 |
[property].[usp_PERSIST_PropertyName_Measure_T]
Statement
|
|
810 |
[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
|
|
910 |
[repo].[usp_GeneratorUsp_insert_update_persistence]
RepoObjectColumn_column_id is required and should be updated before Statement
|
|
920 |
Persistence Target: update repo.RepoObject set InheritanceType = 13 (if NULL)
Statement
|
|
3200 |
[property].[usp_RepoObjectProperty_collect]
Statement
|
|
3310 |
[reference].[usp_PERSIST_additional_Reference_from_properties_tgt]
Statement
|
|
3320 |
[reference].[usp_PERSIST_additional_Reference_wo_columns_from_properties_tgt]
Statement
|
|
3330 |
[reference].[usp_PERSIST_additional_Reference_from_ssas_tgt]
Statement
|
|
3400 |
[property].[usp_external_property_import]
Statement
|
|
4110 |
[property].[usp_RepoObject_Inheritance]
Statement
|
|
4120 |
[property].[usp_RepoObjectColumn_Inheritance]
Statement
|
References
Referenced Objects
-
reference.RepoObject_reference_persistence_target_as_source - V
-
reference.usp_PERSIST_additional_Reference_from_properties_tgt - P
-
reference.usp_PERSIST_additional_Reference_from_ssas_tgt - P
-
reference.usp_PERSIST_additional_Reference_wo_columns_from_properties_tgt - P
-
repo.usp_RepoObjectColumn_update_RepoObjectColumn_column_id - P
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