property.RepoObjectProperty_SelectedPropertyName_split - V

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

RepoObject_guid: 49EFD408-A51F-EC11-8523-A81E8446D5B0

Description

  • property_value of property.RepoObjectProperty - U is splitted in lines and these lines are splitted in rows

  • Where property_name = 'additional_reference_csv'

Examples

Entity Diagram

entity-property.repoobjectproperty_selectedpropertyname_split

Columns

Table 1. Columns of property.RepoObjectProperty_SelectedPropertyName_split - V
PK Column Name Data Type NULL? ID

bigint

NULL

nvarchar(128)

NOT NULL

nvarchar(max)

NULL

uniqueidentifier

NOT NULL

bigint

NULL

nvarchar(max)

NULL

bigint

NULL

nvarchar(max)

NULL

Foreign Key Diagram

entity_1_1_fk-property.repoobjectproperty_selectedpropertyname_split

References

Referenced Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-property.repoobjectproperty_selectedpropertyname_split

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-property.repoobjectproperty_selectedpropertyname_split

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-property.repoobjectproperty_selectedpropertyname_split

Column Reference Diagram

entity_1_1_colref-property.repoobjectproperty_selectedpropertyname_split

Indexes

idx_RepoObjectProperty_SelectedPropertyName_split__1

idx_RepoObjectProperty_SelectedPropertyName_split__2

Column Details

_

LinePerGuidProperty

LinePerGuidProperty

bigint

NULL

property_name

property_name

nvarchar(128)

NOT NULL

property_value

property_value

nvarchar(max)

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

RowPerGuidPropertyLine

RowPerGuidPropertyLine

bigint

NULL

value_line

value_line

nvarchar(max)

NULL

value_line_len

value_line_len

bigint

NULL

value_line_row

value_line_row

nvarchar(max)

NULL

sql_modules_definition

property.RepoObjectProperty_SelectedPropertyName_split - V script
/*
<<property_start>>Description
* property_value of xref:dhw:sqldb:property.repoobjectproperty.adoc[] is splitted in lines and these lines are splitted in rows
* Where property_name = 'additional_reference_csv'
<<property_end>>
*/
CREATE View [property].[RepoObjectProperty_SelectedPropertyName_split]
As
Select
    RepoObject_guid
  , property_name
  , property_value
  , value_line             = Value2LineSplit.value
  , value_line_row         = Line2RowSplit.value
  , value_line_len         = Len ( Value2LineSplit.value )
  , LinePerGuidProperty    = Dense_Rank () Over ( Partition By
                                                      RepoObject_guid
                                                    , property_name
                                                  Order By
                                                      Value2LineSplit.value
                                                )
  , RowPerGuidPropertyLine = Row_Number () Over ( Partition By
                                                      RepoObject_guid
                                                    , property_name
                                                    , Value2LineSplit.value
                                                  Order By
                                                      Value2LineSplit.value
                                                )
From
    property.RepoObjectProperty
    --String_Split separator must be one (1) char (varchar, nchar, nvarchar)
    --we remove Char(13)
    --Windows CR LF 13 10
    --Unix LF 10
    Cross Apply String_Split(property_value, Char ( 10 )) As Value2LineSplit
    Cross Apply String_Split(Value2LineSplit.value, ',') As Line2RowSplit
Where
    property_name                     = 'additional_reference_csv'
    And Len ( Value2LineSplit.value ) > 1