sqlparse.RepoObject_SqlModules_41_from - V
type: V ( view ), modify_date: 2021-10-02 13:23:33
RepoObject_guid: 3F90291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_RepoObject_SqlModules_41_from__1
-
IndexSemanticGroup: no_group
-
[column-join_type]; varchar(16)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_SqlModules_41_from__2
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
[column-json_key]; nvarchar(4000)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_SqlModules_41_from__3
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
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]