sqlparse.RepoObject_SqlModules_42_from_Identifier - V

--todo IdentifierList
--done: bad performance => Persistence of [repo].[RepoObject_SqlModules_41_from]
CREATE View sqlparse.RepoObject_SqlModules_42_from_Identifier
As
--
Select
    --
    T1.RepoObject_guid
  , T1.json_key
  , T1.SysObject_fullname
  , T1.class
  , T1.RowNumber_per_Object
  --todo: we need extra handling when the result of [name] is an IdentifierList and need to exclude them here
  , name                     = Coalesce ( T1.identifier_name, T1.normalized_wo_nolock )
  , name_charindex_dot       = CharIndex ( '.', Coalesce ( T1.identifier_name, T1.normalized_wo_nolock ))
  --some alias we can get from [T1].[identifier_alias]
  --but in case of aaa (NOLOCK) the alias is on the next row: [lag].[normalized_wo_nolock]
  , alias                    = Coalesce ( T1.identifier_alias, lag.normalized_wo_nolock )
  , T1_identifier_alias      = T1.identifier_alias
  , lag_normalized_wo_nolock = lag.normalized_wo_nolock
--, [pre_is_join] = [pre].[is_join]
--, [pre_is_from] = [pre].[is_from]
--, [T1].[patindex_nolock]
--, [lag_patindex_nolock] = [lag].[patindex_nolock]
--, [T1].[SysObject_fullname]
--, [T1].[is_group]
--, [T1].[is_keyword]
--, [T1].[is_whitespace]
--, [T1].[normalized]
--  --,T1.[children]
--, [T1].[normalized_wo_nolock]
--, [T1].[Min_RowNumber_From]
--, [T1].[Min_RowNumber_Where]
--, [T1].[identifier_name]
--, [T1].[identifier_alias]
--, [T1].[join_type]
--, [T1].[is_join]
--, [T1].[is_from]
From
    sqlparse.RepoObject_SqlModules_41_from_T     As T1
    --required filter
    --the predecessor (by [RowNumber_per_Object]) of T1 should be (is_from or is_join)
    --then often T1 contains an identifier
    --todo: sometimes an Identifier is a SELECT statement like '(SELECT ... FROM ...) as abc'
    Inner Join
        sqlparse.RepoObject_SqlModules_41_from_T As pre
            On
            pre.RepoObject_guid              = T1.RepoObject_guid
            And pre.RowNumber_per_Object + 1 = T1.RowNumber_per_Object
            And
            (
                pre.is_join                  = 1
                Or pre.is_from               = 1
            )
    --to extract some alias we need the folowing entry (lag 1)

    Left Outer Join
    (
        Select
            T1.RepoObject_guid
          , T1.RowNumber_per_Object
          , T1.normalized_wo_nolock
        --, [T1].[json_key]
        --, [T1].[SysObject_fullname]
        --, [T1].[class]
        --, [T1].[is_group]
        --, [T1].[is_keyword]
        --, [T1].[is_whitespace]
        --, [T1].[normalized]
        --, [T1].[children]
        --, [T1].[Min_RowNumber_From]
        --, [T1].[Min_RowNumber_GroupBy]
        --, [T1].[Min_RowNumber_Where]
        --, [T1].[identifier_name]
        --, [T1].[identifier_alias]
        --, [T1].[join_type]
        --, [T1].[is_join]
        --, [T1].[is_from]
        --, [T1].[patindex_nolock]
        From
            sqlparse.RepoObject_SqlModules_41_from_T As T1
        Where
            T1.patindex_nolock > 0
    )                                            As lag
        On
        lag.RepoObject_guid                  = T1.RepoObject_guid
        And lag.RowNumber_per_Object - 1     = T1.RowNumber_per_Object
Where
    --we can extract only 'Identifier'
    T1.class                          = 'Identifier'
    --but even using this filter sometimes we get something different, for exampe a select statement
    --we will not handle them
    And T1.normalized_PatIndex_Select = 0
    --exclude UNION
    And Not Exists
(
    Select
        1
    From
        sqlparse.RepoObject_SqlModules_29_1_object_is_union As filter
    Where
        filter.RepoObject_guid = T1.RepoObject_guid
)