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

Description

Examples

Entity Diagram

entity-sqlparse.repoobject_sqlmodules_20_statement_children

Columns

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

1

uniqueidentifier

NOT NULL

2

nvarchar(4000)

NOT NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(max)

NULL

bigint

NULL

nvarchar(261)

NULL

Foreign Key Diagram

entity_1_1_fk-sqlparse.repoobject_sqlmodules_20_statement_children

References

Object Reference Diagram - 1 1

entity_1_1_objectref-sqlparse.repoobject_sqlmodules_20_statement_children

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-sqlparse.repoobject_sqlmodules_20_statement_children

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-sqlparse.repoobject_sqlmodules_20_statement_children

Column Reference Diagram

entity_1_1_colref-sqlparse.repoobject_sqlmodules_20_statement_children

Indexes

PK_RepoObject_SqlModules_20_statement_children

idx_RepoObject_SqlModules_20_statement_children__2

Column Details

_

child0_children

child0_children

nvarchar(max)

NULL

child0_class

child0_class

nvarchar(500)

NULL

child0_is_group

child0_is_group

bit

NULL

child0_is_keyword

child0_is_keyword

bit

NULL

child0_is_whitespace

child0_is_whitespace

bit

NULL

child0_normalized

child0_normalized

nvarchar(max)

NULL

child1_children

child1_children

nvarchar(max)

NULL

child1_class

child1_class

nvarchar(500)

NULL

child1_is_group

child1_is_group

bit

NULL

child1_is_keyword

child1_is_keyword

bit

NULL

child1_is_whitespace

child1_is_whitespace

bit

NULL

child1_normalized

child1_normalized

nvarchar(max)

NULL

child2_children

child2_children

nvarchar(max)

NULL

child2_class

child2_class

nvarchar(500)

NULL

child2_is_group

child2_is_group

bit

NULL

child2_is_keyword

child2_is_keyword

bit

NULL

child2_is_whitespace

child2_is_whitespace

bit

NULL

child2_normalized

child2_normalized

nvarchar(max)

NULL

child3_children

child3_children

nvarchar(max)

NULL

child3_class

child3_class

nvarchar(500)

NULL

child3_is_group

child3_is_group

bit

NULL

child3_is_keyword

child3_is_keyword

bit

NULL

child3_is_whitespace

child3_is_whitespace

bit

NULL

child3_normalized

child3_normalized

nvarchar(max)

NULL

child4_children

child4_children

nvarchar(max)

NULL

child4_class

child4_class

nvarchar(500)

NULL

child4_is_group

child4_is_group

bit

NULL

child4_is_keyword

child4_is_keyword

bit

NULL

child4_is_whitespace

child4_is_whitespace

bit

NULL

child4_normalized

child4_normalized

nvarchar(max)

NULL

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