reference.ftv_RepoObject_ColumReferenceRepoObject - IF
type: IF ( inline function ), modify_date: 2022-01-05 18:06:35
RepoObject_guid: 9BBE898F-9381-EB11-84E9-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
sql_modules_definition
reference.ftv_RepoObject_ColumReferenceRepoObject - IF script
/*
--hier stimmt irgendetwas nicht, es dauert sehr lange und es kommt ein Fehler mit einer Typ-Konvertierung
--based on [repo].[RepoObjectColumn_ReferenceTree]
--return referenced and referencing RepoObject
--Default:
--@Referenced_Depth = 1
--@Referencing_Depth
DECLARE @RepoObject_guid uniqueidentifier
SET @RepoObject_guid = (SELECT RepoObject_guid from [repo].[RepoObject] where RepoObject_fullname = '[repo].[RepoObject_gross]')
SELECT *
FROM [reference].[ftv_RepoObject_ColumReferenceRepoObject](@RepoObject_guid, DEFAULT, DEFAULT)
SELECT *
FROM [reference].[ftv_RepoObject_ColumReferenceRepoObject](@RepoObject_guid, 1, 1)
*/
CREATE Function reference.ftv_RepoObject_ColumReferenceRepoObject
(
@RepoObject_guid UniqueIdentifier
, @Referenced_Depth Int = 1
, @Referencing_Depth Int = 1
)
Returns Table
As
Return
(
With
ro
As
(
--all RepoObject which are [Referenced_RepoObject_guid] or [Referencing_RepoObject_guid]
Select
Distinct
RepoObject_fullname = Referenced_fullname
, RepoObject_guid = Referenced_RepoObject_guid
, RepoObject_type = Referenced_type
, DbmlRelation = Null
, Parameter_RepoObject_guid = @RepoObject_guid
From
reference.RepoObjectColumn_ReferenceTree
Where
Referenced_RepoObject_guid = @RepoObject_guid
Union
Select
Distinct
Referencing_fullname
, Referencing_RepoObject_guid
, Referencing_type
, DbmlRelation = Null
, @RepoObject_guid
From
reference.RepoObjectColumn_ReferenceTree
Where
Referencing_RepoObject_guid = @RepoObject_guid
Union
--add all referenced
Select
Distinct
rt.Referenced_fullname
, rt.Referenced_RepoObject_guid
, rt.Referenced_type
, rs.DbmlRelation
, @RepoObject_guid
From
reference.RepoObjectColumn_ReferenceTree As rt
Left Join
reference.RepoObjectColumn_RelationScript As rs
On
rs.referenced_RepoObject_guid = rt.Referenced_RepoObject_guid
And rs.referencing_RepoObject_guid = @RepoObject_guid
Where
rt.Referencing_RepoObject_guid = @RepoObject_guid
And rt.Referenced_Depth <= @Referenced_Depth
And rt.Referencing_Depth = 0
Union
--add all referenced
Select
Distinct
rt.Referencing_fullname
, rt.Referencing_RepoObject_guid
, rt.Referencing_type
, rs.DbmlRelation
, @RepoObject_guid
From
reference.RepoObjectColumn_ReferenceTree As rt
Left Join
reference.RepoObjectColumn_RelationScript As rs
On
rs.referenced_RepoObject_guid = @RepoObject_guid
And rs.referencing_RepoObject_guid = rt.Referencing_RepoObject_guid
Where
rt.Referenced_RepoObject_guid = @RepoObject_guid
And rt.Referenced_Depth = 0
And rt.Referencing_Depth <= @Referencing_Depth
)
--
Select
ro.RepoObject_fullname
, ro.RepoObject_guid
, ro.RepoObject_type
, Dbml = dbml.DbmlTable
, ro.Parameter_RepoObject_guid
From
ro
Left Join
repo.RepoObject_SqlCreateTable As dbml
On
dbml.RepoObject_guid = ro.RepoObject_guid
Union
Select
Null
, ro.Parameter_RepoObject_guid
, Null
, ro.DbmlRelation
, ro.Parameter_RepoObject_guid
From
ro
Where
Not ro.DbmlRelation Is Null
--
);