MSSQL - issues with views, containing comments => newlines are missing and code gets invalide

[aisbergde]

I don't know if this is related to the used database driver or to the program, but I have a lot of issues with view which are showing in the diagram with strange column information.

MSSQL Server 2019, Microsoft JDBC Driver 9.2

some issues are related to removed linebreaks which makes some valid SQL invalid, if comment should be from "--" to the end of the line. But it looks like there could be also other reasons for issues.

some examples (I will take the SQL code as it is shown in MogwaiERDesigner)

CREATE VIEW repo.visjs_EdgeList_object_test01 AS /*  <script type="text/javascript">      // create an array with nodes      var nodes = new vis.DataSet([          {id: 1, label: 'Node 1'},          {id: 2, label: 'Node 2'},          {id: 3, label: 'Node 3'},          {id: 4, label: 'Node 4'},          {id: 5, label: 'Node 5'}      ]);        // create an array with edges      var edges = new vis.DataSet([          {from: 1, to: 3},          {from: 1, to: 2},          {from: 2, to: 4},          {from: 2, to: 5}      ]);    */  CREATE VIEW [repo].[visjs_EdgeList_object_test01]  AS  SELECT [referencing_node_id]   , [referenced_node_id]   , EdgeListElement = CONCAT (    '{ from: '    , [referenced_node_id]    , ', to:'    , [referencing_node_id]    , ' },'    )  FROM repo.[RepoObject_reference_SqlExpressionDependencies];

CREATE VIEW repo.Index_IndexPattern AS --  SELECT [index_guid]   , IndexPatternColumnName = String_Agg(SysObject_column_name, ';') WITHIN  GROUP (    ORDER BY [index_column_id]    )   , IndexPatternColumnDatatype = String_Agg([SysObject_column_user_type_fullname], ';') WITHIN  GROUP (    ORDER BY [index_column_id]    )  FROM [repo].[IndexColumn_union]  WHERE NOT [index_guid] IS NULL  GROUP BY [index_guid];

original

--contains only PK or UNIQUE
--requirement:
-- - repo.Index_Settings.is_create_constraint = 1
-- - repo.Index_union.is_index_unique = 1
CREATE VIEW [repo].[Index_SqlConstraint_PkUq]
AS
SELECT [i].[index_guid]
 , [i].[parent_RepoObject_guid]
 , [SqlConstraint] = CONCAT (
  'CONSTRAINT '
  --todo missing name?
  , QUOTENAME([i].[index_name])
  , ' '
  , CASE 
   WHEN [i].[is_index_primary_key] = 1
    THEN 'PRIMARY KEY '
   WHEN [i].[is_index_unique] = 1
    THEN 'UNIQUE '
   END
  , CASE [i].[index_type]
   WHEN 1
    THEN 'CLUSTERED '
   WHEN 2
    THEN 'NONCLUSTERED '
   END
  , '('
  , [ColumnList].[ConstraintColumnList]
  , ')'
  )
-- , i.index_name
-- , i.index_type
-- , i.is_index_unique
-- , i.is_index_primary_key
-- --, i.referenced_index_guid
-- , i.is_index_disabled
----, i.is_index_real
----, i_s.is_create_constraint
FROM repo.Index_union AS i
LEFT OUTER JOIN repo.Index_Settings AS i_s
 ON i_s.index_guid = i.index_guid
LEFT OUTER JOIN repo.Index_ColumList AS ColumnList
 ON ColumnList.[index_guid] = i.[index_guid]
WHERE [i_s].[is_create_constraint] = 1
 AND [i].[is_index_unique] = 1
GO

in ERDesignerNG

CREATE VIEW repo.Index_SqlConstraint_PkUq AS --contains only PK or UNIQUE  --requirement:  -- - repo.Index_Settings.is_create_constraint = 1  -- - repo.Index_union.is_index_unique = 1  CREATE VIEW [repo].[Index_SqlConstraint_PkUq]  AS  SELECT [i].[index_guid]   , [i].[parent_RepoObject_guid]   , [SqlConstraint] = CONCAT (    'CONSTRAINT '    --todo missing name?    , QUOTENAME([i].[index_name])    , ' '    , CASE      WHEN [i].[is_index_primary_key] = 1      THEN 'PRIMARY KEY '     WHEN [i].[is_index_unique] = 1      THEN 'UNIQUE '     END    , CASE [i].[index_type]     WHEN 1      THEN 'CLUSTERED '     WHEN 2      THEN 'NONCLUSTERED '     END    , '('    , [ColumnList].[ConstraintColumnList]    , ')'    )  -- , i.index_name  -- , i.index_type  -- , i.is_index_unique  -- , i.is_index_primary_key  -- --, i.referenced_index_guid  -- , i.is_index_disabled  ----, i.is_index_real  ----, i_s.is_create_constraint  FROM repo.Index_union AS i  LEFT OUTER JOIN repo.Index_Settings AS i_s   ON i_s.index_guid = i.index_guid  LEFT OUTER JOIN repo.Index_ColumList AS ColumnList   ON ColumnList.[index_guid] = i.[index_guid]  WHERE [i_s].[is_create_constraint] = 1   AND [i].[is_index_unique] = 1;

CREATE   VIEW [repo].[InheritanceType]
AS
--
SELECT
       [InheritanceType] = 0
     , [InheritanceTypeDescription] = 'No inheritance from predecessor'
--
UNION ALL
SELECT
       [InheritanceType] = 11
     , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, if current value is NULL'
UNION ALL
SELECT
       [InheritanceType] = 12
     , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, if current value is NULL or empty ('''')'
UNION ALL
SELECT
       [InheritanceType] = 13
     , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, forced, only when source is not empty'
UNION ALL
SELECT
       [InheritanceType] = 14
     , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, forced without exception (dangerous!)'
----
--UNION ALL
--SELECT
--       [InheritanceType] = 21
--     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), if current value is NULL'
--UNION ALL
--SELECT
--       [InheritanceType] = 22
--     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), if current value is NULL or empty ('''')'
--UNION ALL
--SELECT
--       [InheritanceType] = 23
--     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), forced, only when source is not empty'
--UNION ALL
--SELECT
--       [InheritanceType] = 24
--     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), forced without exception (dangerous!)'

----still unclear if and how this could or should be implemented
----additional parameters for CONCAT String required
--UNION ALL
--SELECT
--       [InheritanceType] = 31
--     , [InheritanceTypeDescription] = 'use CONCAT (for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''), Inheritance from first predecessor, if current value is NULL'
--UNION ALL
--SELECT
--       [InheritanceType] = 32
--     , [InheritanceTypeDescription] = 'use CONCAT (for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''), Inheritance from first predecessor, if current value is NULL or empty ('''')'
--UNION ALL
--SELECT
--       [InheritanceType] = 33
--     , [InheritanceTypeDescription] = 'use CONCAT (for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''), Inheritance from first predecessor, force'

in ERDesigner

CREATE VIEW repo.InheritanceType AS --  SELECT         [InheritanceType] = 0       , [InheritanceTypeDescription] = 'No inheritance from predecessor'  --  UNION ALL  SELECT         [InheritanceType] = 11       , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, if current value is NULL'  UNION ALL  SELECT         [InheritanceType] = 12       , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, if current value is NULL or empty ('''')'  UNION ALL  SELECT         [InheritanceType] = 13       , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, forced, only when source is not empty'  UNION ALL  SELECT         [InheritanceType] = 14       , [InheritanceTypeDescription] = 'Inheritance from first (or all) predecessor, forced without exception (dangerous!)'  ----  --UNION ALL  --SELECT  --       [InheritanceType] = 21  --     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), if current value is NULL'  --UNION ALL  --SELECT  --       [InheritanceType] = 22  --     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), if current value is NULL or empty ('''')'  --UNION ALL  --SELECT  --       [InheritanceType] = 23  --     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), forced, only when source is not empty'  --UNION ALL  --SELECT  --       [InheritanceType] = 24  --     , [InheritanceTypeDescription] = 'Inheritance from all predecessors - STRING_AGG(xyz, CHAR(13)+CHAR(10)), forced without exception (dangerous!)'    ----still unclear if and how this could or should be implemented  ----additional parameters for CONCAT String required  --UNION ALL  --SELECT  --       [InheritanceType] = 31  --     , [InheritanceTypeDescription] = 'use CONCAT (for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''), Inheritance from first predecessor, if current value is NULL'  --UNION ALL  --SELECT  --       [InheritanceType] = 32  --     , [InheritanceTypeDescription] = 'use CONCAT (for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''), Inheritance from first predecessor, if current value is NULL or empty ('''')'  --UNION ALL  --SELECT  --       [InheritanceType] = 33  --     , [InheritanceTypeDescription] = 'use CONCAT (for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''), Inheritance from first predecessor, force';

These are only some examples.

Nearly all views have issues, and I think most time this is because of removed linebreaks.

It would be better to read not only the code of views but the metadata (columns and their type) because this metadata is correctly available in system views. The code should be only used as it is, with containing line breaks, not trying to parse it. It will never be possible to parse all possible views correctly.