workflow.Workflow_ProcedureDependency_T_redundant - V
type: V ( view ), modify_date: 2021-08-17 20:29:03
RepoObject_guid: A1BCD983-91FA-EB11-850E-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_Workflow_ProcedureDependency_T_redundant__1
-
IndexSemanticGroup: no_group
-
[column-Workflow_id]; int
-
[column-referenced_Procedure_RepoObject_guid]; uniqueidentifier
-
[column-referencing_Procedure_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_Workflow_ProcedureDependency_T_redundant__2
-
IndexSemanticGroup: no_group
-
[column-Workflow_id]; int
-
-
PK, Unique, Real: 0, 0, 0
idx_Workflow_ProcedureDependency_T_redundant__3
-
IndexSemanticGroup: no_group
-
[column-referenced_Procedure_RepoObject_guid]; uniqueidentifier
-
[column-referencing_Procedure_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_Workflow_ProcedureDependency_T_redundant__4
-
IndexSemanticGroup: no_group
-
[column-referenced_Procedure_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_Workflow_ProcedureDependency_T_redundant__5
-
IndexSemanticGroup: no_group
-
[column-referencing_Procedure_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
sql_modules_definition
workflow.Workflow_ProcedureDependency_T_redundant - V script
/*
possible redundant references
we must not remove all of them at the same time, because some of them recognize each other as redundant. +
Therefore marking as `is_redundant` happens one by one for always only one reference by referencing_Procedure_RepoObject_guid
*/
CREATE View workflow.Workflow_ProcedureDependency_T_redundant
As
Select
T1.Workflow_id
, T1.referenced_Procedure_RepoObject_guid
, T1.referencing_Procedure_RepoObject_guid
, RownrPerReferencing = Row_Number () Over ( Partition By
T1.Workflow_id
, T1.referencing_Procedure_RepoObject_guid
Order By
T1.referenced_Procedure_RepoObject_guid
)
From
workflow.Workflow_ProcedureDependency_T_active As T1
Inner Join
workflow.Workflow_ProcedureDependency_T_active As T2
On
T1.Workflow_id = T2.Workflow_id
And T1.referenced_Procedure_RepoObject_guid = T2.referenced_Procedure_RepoObject_guid
Inner Join
workflow.Workflow_ProcedureDependency_T_active As T3
On
T1.Workflow_id = T3.Workflow_id
And T1.referencing_Procedure_RepoObject_guid = T3.referencing_Procedure_RepoObject_guid
And T2.referencing_Procedure_RepoObject_guid = T3.referenced_Procedure_RepoObject_guid
Where
T1.is_redundant = 0
And T2.is_redundant = 0
And T3.is_redundant = 0