sqlparse.RepoObject_SqlModules_41_from - V

type: V ( view ), modify_date: 2021-10-02 13:23:33

RepoObject_guid: 3F90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-sqlparse.repoobject_sqlmodules_41_from

Columns

Table 1. Columns of sqlparse.RepoObject_SqlModules_41_from - V
PK Column Name Data Type NULL? ID

nvarchar(max)

NULL

nvarchar(500)

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

int

NOT NULL

bit

NULL

int

NOT NULL

bit

NULL

bit

NULL

varchar(16)

NULL

nvarchar(4000)

NOT NULL

bigint

NULL

bigint

NULL

bigint

NULL

nvarchar(max)

NULL

bigint

NULL

nvarchar(max)

NULL

bigint

NULL

uniqueidentifier

NOT NULL

bigint

NULL

nvarchar(261)

NULL

Foreign Key Diagram

entity_1_1_fk-sqlparse.repoobject_sqlmodules_41_from

References

Object Reference Diagram - 1 1

entity_1_1_objectref-sqlparse.repoobject_sqlmodules_41_from

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-sqlparse.repoobject_sqlmodules_41_from

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-sqlparse.repoobject_sqlmodules_41_from

Column Reference Diagram

entity_1_1_colref-sqlparse.repoobject_sqlmodules_41_from

Indexes

idx_RepoObject_SqlModules_41_from__1

idx_RepoObject_SqlModules_41_from__2

idx_RepoObject_SqlModules_41_from__3

Column Details

_

children

children

nvarchar(max)

NULL

class

class

nvarchar(500)

NULL

identifier_alias

identifier_alias

nvarchar(max)

NULL

identifier_name

identifier_name

nvarchar(max)

NULL

is_from

is_from

int

NOT NULL

is_join

is_join

int

NOT NULL

join_type

join_type

varchar(16)

NULL

Referenced Columns

json_key

json_key

nvarchar(4000)

NOT NULL

Min_RowNumber_From

Min_RowNumber_From

bigint

NULL

Min_RowNumber_GroupBy

Min_RowNumber_GroupBy

bigint

NULL

Min_RowNumber_Where

Min_RowNumber_Where

bigint

NULL

normalized

normalized

nvarchar(max)

NULL

normalized_PatIndex_Select

normalized_PatIndex_Select

bigint

NULL

normalized_wo_nolock

normalized_wo_nolock

nvarchar(max)

NULL

patindex_nolock

patindex_nolock

bigint

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

RowNumber_per_Object

RowNumber_per_Object

bigint

NULL

SysObject_fullname

SysObject_fullname

nvarchar(261)

NULL

Description

(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))


sql_modules_definition

sqlparse.RepoObject_SqlModules_41_from - V script
/*
rows in [repo].[RepoObject__sql_modules_20_statement_children]
which define the first Block
- between FROM and WHERE
- or between FROM and GROUP BY

Attention, this will not work for UNION to analyze all parts of the UNION
- we could get the first part
- we could get the part from the first from to a first where in another part of the UNION
*/

CREATE View sqlparse.RepoObject_SqlModules_41_from
As
--
Select
    T1.RepoObject_guid
  , T1.json_key
  , T1.SysObject_fullname
  , T1.RowNumber_per_Object
  , T1.class
  , T1.is_group
  , T1.is_keyword
  , T1.is_whitespace
  , T1.normalized
  , T1.children
  , normalized_PatIndex_Select = PatIndex ( '%SELECT%', T1.normalized )
  , normalized_wo_nolock       = Trim ( Replace ( T1.normalized, '(NOLOCK)', '' ))
  --, [T23_normalized_wo_nolock] = [T23].[normalized_wo_nolock]
  , T2.Min_RowNumber_From
  , T2.Min_RowNumber_GroupBy
  , T2.Min_RowNumber_Where
  , T22.identifier_name
  , T22.identifier_alias
  , T4.join_type
  , is_join                    = Iif(Not T4.join_type Is Null, 1, 0)
  , is_from                    = Iif(T1.normalized = 'FROM' And T1.is_keyword = 1, 1, 0)
  , patindex_nolock            = PatIndex ( '%(NOLOCK)%', T1.normalized )
From
    sqlparse.RepoObject_SqlModules_20_statement_children      As T1
    Left Outer Join
        sqlparse.RepoObject_SqlModules_39_object              As T2
            On
            T2.RepoObject_guid   = T1.RepoObject_guid

    Left Outer Join
        sqlparse.RepoObject_SqlModules_22_identifier_alias_AS As T22
            On
            T22.RepoObject_guid  = T1.RepoObject_guid
            And T22.json_key     = T1.json_key
    --LEFT OUTER JOIN
    --[repo].[RepoObject__sql_modules_23_normalized_wo_nolock] AS T23
    --ON T23.[RepoObject_guid] = T1.[RepoObject_guid]
    --   AND T23.[key] = T1.[key]

    Left Outer Join
        configT.join_type                                     As T4
            On
            T4.join_type_variant = T1.normalized
            And T1.is_keyword    = 1
Where
    --extract the FROM part:
    --start: [Min_RowNumber_From]
    T2.Min_RowNumber_From           <= T1.RowNumber_per_Object
    --ende: [Min_RowNumber_Where] or [Min_RowNumber_GroupBy]
    And
    (
        T2.Min_RowNumber_Where Is Null
        Or T2.Min_RowNumber_Where   > T1.RowNumber_per_Object
    )
    And
    (
        T2.Min_RowNumber_GroupBy Is Null
        Or T2.Min_RowNumber_GroupBy > T1.RowNumber_per_Object
    )
--ORDER BY
--         [T1].[RepoObject_guid]
--       , [T1].[RowNumber_per_Object]