sqlparse.RepoObject_SqlModules_20_statement_children - V
type: V ( view ), modify_date: 2021-08-17 20:05:35
RepoObject_guid: F78F291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
1 |
|
|
||
2 |
|
|
||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
References
Indexes
PK_RepoObject_SqlModules_20_statement_children
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
[column-json_key]; nvarchar(4000)
-
-
PK, Unique, Real: 1, 1, 0
idx_RepoObject_SqlModules_20_statement_children__2
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
RepoObject_guid
1 |
RepoObject_guid |
|
|
-
sqlparse.RepoObject_SqlModules_21_statement_children_helper.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_22_identifier_alias_AS.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_23_normalized_wo_nolock.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_24_IdentifierList_children.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_29_1_object_is_union.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_29_2_object_is_GroupBy.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_32_ObjectClass.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_33_ObjectNormalized.RepoObject_guid
-
sqlparse.RepoObject_SqlModules_51_Identitfier.RepoObject_guid
json_key
2 |
json_key |
|
|
class
class |
|
|
normalized
normalized |
|
|
-
sqlparse.RepoObject_SqlModules_21_statement_children_helper.normalized
-
sqlparse.RepoObject_SqlModules_22_identifier_alias_AS.identifier_name
-
sqlparse.RepoObject_SqlModules_23_normalized_wo_nolock.normalized
-
sqlparse.RepoObject_SqlModules_24_IdentifierList_children.normalized
-
sqlparse.RepoObject_SqlModules_33_ObjectNormalized.normalized
RowNumber_per_Object
RowNumber_per_Object |
|
|
SysObject_fullname
SysObject_fullname |
|
|
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
-
sqlparse.RepoObject_SqlModules_21_statement_children_helper.SysObject_fullname
-
sqlparse.RepoObject_SqlModules_22_identifier_alias_AS.SysObject_fullname
-
sqlparse.RepoObject_SqlModules_23_normalized_wo_nolock.SysObject_fullname
-
sqlparse.RepoObject_SqlModules_24_IdentifierList_children.SysObject_fullname
-
sqlparse.RepoObject_SqlModules_32_ObjectClass.SysObject_fullname
-
sqlparse.RepoObject_SqlModules_33_ObjectNormalized.SysObject_fullname
-
sqlparse.RepoObject_SqlModules_51_Identitfier.SysObject_fullname
-
sqlparse.RepoObject_SqlModules_Identitfier.SysObject_fullname
sql_modules_definition
sqlparse.RepoObject_SqlModules_20_statement_children - V script
/*
Assuming that a statement has only one child, this one child is decomposed into its components here.
In the next step the reiehenfolge can be checked
For example
- CREATE
- VIEW
- (identifier)
- AS
- SELECT
It can be checked if there are other components between SELECT and IdentifierList, like DISTINCT
- SELECT
- optional ... (DISTINCT, TOP xyz, ...)
- IdentifierList
- FROM
- FROM
- ...
- WHERE / HAVING
and so on
----old sql:
--SELECT [T1].[RepoObject_guid]
-- --we need the key for ROW_NUMBER
-- --key is an int in this case, maybe because the json is an array
-- , [j1].[key]
-- , [T1].[SysObject_fullname]
-- --a statement should have only one child
-- --if this is not the case we need to include into ROW_NUMBER()
-- --, T1.[children]
-- , [RowNumber_per_Object] = ROW_NUMBER() OVER (
-- PARTITION BY [T1].[RepoObject_guid] ORDER BY TRY_CAST([j1].[key] AS INT)
-- )
-- --, j1.[value]
-- --, j1.[type]
-- , [j2].*
--FROM [repo].[RepoObject_SqlModules_10_statement] AS T1
--CROSS APPLY OPENJSON(T1.[children]) AS j1
--CROSS APPLY OPENJSON(j1.[value]) WITH (
-- class NVARCHAR(500) N'$.class'
-- , is_group BIT N'$.is_group'
-- , is_keyword BIT N'$.is_keyword'
-- , is_whitespace BIT N'$.is_whitespace'
-- , normalized NVARCHAR(MAX) N'$.normalized'
-- , children NVARCHAR(MAX) N'$.children' AS JSON
-- ) AS j2
*/
CREATE View sqlparse.RepoObject_SqlModules_20_statement_children
As
--
Select
T1.RepoObject_guid
--we need the key for ROW_NUMBER
--key is an int in this case, maybe because the json is an array
, json_key = T2.json_key Collate Database_Default
, T1.SysObject_fullname
--a statement should have only one child
--if this is not the case we need to include into ROW_NUMBER()
--, T1.[children]
, RowNumber_per_Object = Row_Number () Over ( Partition By
T1.RepoObject_guid
Order By
Try_Cast(T2.json_key As Int)
)
, T2.class
, T2.is_group
, T2.is_keyword
, T2.is_whitespace
, T2.normalized
, T2.children
, T2.child0_class
, T2.child0_is_group
, T2.child0_is_keyword
, T2.child0_is_whitespace
, T2.child0_normalized
, T2.child0_children
, T2.child1_class
, T2.child1_is_group
, T2.child1_is_keyword
, T2.child1_is_whitespace
, T2.child1_normalized
, T2.child1_children
, T2.child2_class
, T2.child2_is_group
, T2.child2_is_keyword
, T2.child2_is_whitespace
, T2.child2_normalized
, T2.child2_children
, T2.child3_class
, T2.child3_is_group
, T2.child3_is_keyword
, T2.child3_is_whitespace
, T2.child3_normalized
, T2.child3_children
, T2.child4_class
, T2.child4_is_group
, T2.child4_is_keyword
, T2.child4_is_whitespace
, T2.child4_normalized
, T2.child4_children
From
sqlparse.RepoObject_SqlModules_10_statement As T1
Cross Apply sqlparse.ftv_sqlparse_with_some_children ( T1.children ) As T2