logs.ExecutionLog_gross - V

type: V ( view ), modify_date: 2022-06-30 17:01:04

RepoObject_guid: BB90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Example 1. Usage
--get detailed PlantUML sequence diagram
--you could add skinparam to modify it, for example
--skinparam maxmessagesize 250

Select
    id
  , puml_Sequence
  , execution_instance_guid
  , parent_execution_log_id
  , proc_schema_name
  , proc_name
  , step_id
  , step_name
  , created_dt
  , source_object
  , target_object
  , inserted
  , updated
  , deleted
From
    logs.ExecutionLog_gross
Where
    id
Between 33386 And 33404
Order By
    id;

Entity Diagram

entity-logs.executionlog_gross

Columns

Table 1. Columns of logs.ExecutionLog_gross - V
PK Column Name Data Type NULL? ID

datetime

NULL

datetime

NULL

datetime

NULL

uniqueidentifier

NULL

int

NULL

int

NULL

int

NULL

int

NULL

nvarchar(max)

NULL

uniqueidentifier

NOT NULL

id

bigint

NOT NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

int

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

bigint

NULL

int

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

nvarchar(1550)

NOT NULL

nvarchar(261)

NULL

bigint

NULL

int

NULL

nvarchar(1000)

NULL

int

NULL

nvarchar(261)

NULL

int

NULL

Foreign Key Diagram

entity_1_1_fk-logs.executionlog_gross

References

Referenced Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-logs.executionlog_gross

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-logs.executionlog_gross

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-logs.executionlog_gross

Column Reference Diagram

entity_1_1_colref-logs.executionlog_gross

Indexes

idx_ExecutionLog_gross__1

  • IndexSemanticGroup: no_group

    • id; bigint

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

Column Details

_

created_dt

created_dt

datetime

NULL

Referenced Columns

created_dt_max_execution_instance_guid

created_dt_max_execution_instance_guid

datetime

NULL

created_dt_min_execution_instance_guid

created_dt_min_execution_instance_guid

datetime

NULL

current_execution_guid

current_execution_guid

uniqueidentifier

NULL

deleted

deleted

int

NULL

Referenced Columns

duration_current_execution_guid

duration_current_execution_guid

int

NULL

duration_execution_instance_guid

duration_execution_instance_guid

int

NULL

duration_step

duration_step

int

NULL

event_info

event_info

nvarchar(max)

NULL

Referenced Columns

execution_instance_guid

execution_instance_guid

uniqueidentifier

NOT NULL

id

id

bigint

NOT NULL

Referenced Columns

info_01

info_01

nvarchar(4000)

NULL

Referenced Columns

info_02

info_02

nvarchar(4000)

NULL

Referenced Columns

info_03

info_03

nvarchar(4000)

NULL

Referenced Columns

info_04

info_04

nvarchar(4000)

NULL

Referenced Columns

info_05

info_05

nvarchar(4000)

NULL

Referenced Columns

info_06

info_06

nvarchar(4000)

NULL

Referenced Columns

info_07

info_07

nvarchar(4000)

NULL

Referenced Columns

info_08

info_08

nvarchar(4000)

NULL

Referenced Columns

info_09

info_09

nvarchar(4000)

NULL

Referenced Columns

inserted

inserted

int

NULL

Referenced Columns

parameter_01

parameter_01

nvarchar(4000)

NULL

Referenced Columns

parameter_02

parameter_02

nvarchar(4000)

NULL

Referenced Columns

parameter_03

parameter_03

nvarchar(4000)

NULL

Referenced Columns

parameter_04

parameter_04

nvarchar(4000)

NULL

Referenced Columns

parameter_05

parameter_05

nvarchar(4000)

NULL

Referenced Columns

parameter_06

parameter_06

nvarchar(4000)

NULL

Referenced Columns

parameter_07

parameter_07

nvarchar(4000)

NULL

Referenced Columns

parameter_08

parameter_08

nvarchar(4000)

NULL

Referenced Columns

parameter_09

parameter_09

nvarchar(4000)

NULL

Referenced Columns

parameter_10

parameter_10

nvarchar(4000)

NULL

Referenced Columns

parameter_11

parameter_11

nvarchar(4000)

NULL

Referenced Columns

parameter_12

parameter_12

nvarchar(4000)

NULL

Referenced Columns

parameter_13

parameter_13

nvarchar(4000)

NULL

Referenced Columns

parameter_14

parameter_14

nvarchar(4000)

NULL

Referenced Columns

parameter_15

parameter_15

nvarchar(4000)

NULL

Referenced Columns

parameter_16

parameter_16

nvarchar(4000)

NULL

Referenced Columns

parameter_17

parameter_17

nvarchar(4000)

NULL

Referenced Columns

parameter_18

parameter_18

nvarchar(4000)

NULL

Referenced Columns

parameter_19

parameter_19

nvarchar(4000)

NULL

Referenced Columns

parameter_20

parameter_20

nvarchar(4000)

NULL

Referenced Columns

parent_execution_log_id

parent_execution_log_id

bigint

NULL

proc_id

proc_id

int

NULL

Referenced Columns

proc_name

proc_name

nvarchar(128)

NULL

Referenced Columns

proc_schema_name

proc_schema_name

nvarchar(128)

NULL

puml_Sequence

puml_Sequence

nvarchar(1550)

NOT NULL

source_object

source_object

nvarchar(261)

NULL

Referenced Columns

ssis_execution_id

ssis_execution_id

bigint

NULL

step_id

step_id

int

NULL

Referenced Columns

step_name

step_name

nvarchar(1000)

NULL

Referenced Columns

sub_execution_id

sub_execution_id

int

NULL

target_object

target_object

nvarchar(261)

NULL

Referenced Columns

updated

updated

int

NULL

Referenced Columns

sql_modules_definition

logs.ExecutionLog_gross - V script
/*
<<property_start>>exampleUsage
--get detailed PlantUML sequence diagram
--you could add skinparam to modify it, for example
--skinparam maxmessagesize 250

Select
    id
  , puml_Sequence
  , execution_instance_guid
  , parent_execution_log_id
  , proc_schema_name
  , proc_name
  , step_id
  , step_name
  , created_dt
  , source_object
  , target_object
  , inserted
  , updated
  , deleted
From
    logs.ExecutionLog_gross
Where
    id
Between 33386 And 33404
Order By
    id;
<<property_end>>
*/
CREATE View [logs].[ExecutionLog_gross]
As
Select
    id
  , parent_execution_log_id
  , duration_current_execution_guid         = DateDiff (
                                                           ss
                                                         , Min ( created_dt ) Over ( Partition By current_execution_guid )
                                                         , Max ( created_dt ) Over ( Partition By current_execution_guid )
                                                       )
  , duration_execution_instance_guid        = DateDiff (
                                                           ss
                                                         , Min ( created_dt ) Over ( Partition By execution_instance_guid )
                                                         , Max ( created_dt ) Over ( Partition By execution_instance_guid )
                                                       )
  , duration_step                           = Iif(Not step_name In ( 'start', 'end' )
                        , DateDiff ( ss, Lag ( created_dt ) Over ( Order By id ), created_dt )
                        , Null)
  , created_dt
  , proc_schema_name
  , proc_name
  , step_id
  , step_name
  , inserted
  , updated
  , deleted
  , source_object
  , target_object
  , execution_instance_guid
  , ssis_execution_id
  , sub_execution_id
  , current_execution_guid
  , proc_id
  , info_01
  , info_02
  , info_03
  , info_04
  , info_05
  , info_06
  , info_07
  , info_08
  , info_09
  , event_info
  , parameter_01
  , parameter_02
  , parameter_03
  , parameter_04
  , parameter_05
  , parameter_06
  , parameter_07
  , parameter_08
  , parameter_09
  , parameter_10
  , parameter_11
  , parameter_12
  , parameter_13
  , parameter_14
  , parameter_15
  , parameter_16
  , parameter_17
  , parameter_18
  , parameter_19
  , parameter_20
  --
  , created_dt_min_execution_instance_guid = Min ( created_dt ) Over ( Partition By execution_instance_guid )
  , created_dt_max_execution_instance_guid = Max ( created_dt ) Over ( Partition By execution_instance_guid )
  --
  , puml_Sequence                           = --
  --combine the result with
  --skinparam maxmessagesize 250
  Case
      When source_object Is Null
           And target_object Is Null
          Then
          Concat (
                     Char ( 13 ) + Char ( 10 )
                   , '== '
                   , concat(quotename([proc_schema_name]),'.',quotename([proc_name])) --proc_fullname
                   , ' - '
                   , step_name
                   , ' =='
                   , Char ( 13 ) + Char ( 10 )
                   , Char ( 13 ) + Char ( 10 )
                 )
      Else
          Concat (
                     --
                     Case
                         When Not source_object Is Null
                             Then
                             QuoteName ( source_object, '"' )
                     End
                   , Case
                         When Not inserted Is Null
                             Then
                             Case
                                 When source_object Is Null
                                     Then
                                     '?-> '
                                 Else
                                     ' -> '
                             End
                         When Not updated Is Null
                             Then
                             Case
                                 When source_object Is Null
                                     Then
                                     '?->o '
                                 Else
                                     ' ->O '
                             End
                         When Not deleted Is Null
                             Then
                             Case
                                 When source_object Is Null
                                     Then
                                     '?->x '
                                 Else
                                     ' ->x '
                             End
                         Else
                             Case
                                 When source_object Is Null
                                     Then
                                     '?--> '
                                 Else
                                     ' --> '
                             End
                     End
                   , Case
                         When Not target_object Is Null
                             Then
                             QuoteName ( target_object, '"' )
                     End
                   --
                   --, ' : ' , QUOTENAME([step_name] , '"')
                   , ' : '
                   , step_name
                   , Char ( 13 ) + Char ( 10 )
                   -- "r" - rectangle note
                   , 'rnote right:'
                   , Coalesce ( inserted, updated, deleted )
                 --
                 )
  End
From
    logs.ExecutionLog