property.RepoObjectProperty_Collect_source_sql_modules_definition - V

/*
<<property_start>>Description
* extracts properties from sql_modules_definition
* properties can be added (normally in comments) following this syntax
** use one line to mark the starting point for parsing: `\<<property_start>>abcde` markes the property_name `abcde`
** use one line to mark the end point: `\<<property_end>>`
* The correct sequence must be followed. Nested parsing is not supported.
<<property_end>>
*/
CREATE View property.RepoObjectProperty_Collect_source_sql_modules_definition
As
Select
    --
    RepoObject_guid
  , property_name  = Trim ( Cast(es.substring_netPreEol As NVarchar(128)))
  , property_value = Cast(String_Agg ( Cast(es.substring_netPostEol As NVarchar(Max)), Char ( 13 ) + Char ( 10 )) Within Group(Order By
                                                                                                                                   es.pos1) As NVarchar(Max))
From
    sqlparse.RepoObject_SqlModules_Repo_Sys
    Cross Apply tool.ftv_extract_substrings (
                                                sql_modules_definition
                                              , Char ( 13 ) + Char ( 10 ) + '<<property_start>>'
                                              , Char ( 13 ) + Char ( 10 ) + '<<property_end>>'
                                            ) As es
Where
    es.substring_netPreEol <> ''
Group By
    RepoObject_guid
  , es.substring_netPreEol