ssas.model_json_311_tables_columns - V

type: V ( view ), modify_date: 2021-09-01 08:42:08

RepoObject_guid: F25FD8EE-E90A-EC11-8516-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssas.model_json_311_tables_columns

Columns

Table 1. Columns of ssas.model_json_311_tables_columns - V
PK Column Name Data Type NULL? ID

1

nvarchar(128)

NOT NULL

2

nvarchar(128)

NOT NULL

3

nvarchar(128)

NULL

uniqueidentifier

NOT NULL

nvarchar(500)

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(500)

NULL

nvarchar(500)

NULL

nvarchar(500)

NULL

nvarchar(500)

NULL

nvarchar(500)

NULL

Foreign Key Diagram

entity_1_1_fk-ssas.model_json_311_tables_columns

References

Referenced Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-ssas.model_json_311_tables_columns

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssas.model_json_311_tables_columns

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssas.model_json_311_tables_columns

Column Reference Diagram

entity_1_1_colref-ssas.model_json_311_tables_columns

Indexes

PK_model_json_311_tables_columns

idx_model_json_311_tables_columns__2

idx_model_json_311_tables_columns__3

Column Details

_

databasename

1

databasename

nvarchar(128)

NOT NULL

tables_name

2

tables_name

nvarchar(128)

NOT NULL

tables_columns_name

3

tables_columns_name

nvarchar(128)

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

tables_columns_dataType

tables_columns_dataType

nvarchar(500)

NULL

tables_columns_description

tables_columns_description

nvarchar(max)

NULL

tables_columns_description_ja

tables_columns_description_ja

nvarchar(max)

NULL

tables_columns_displayFolder

tables_columns_displayFolder

nvarchar(500)

NULL

tables_columns_expression

tables_columns_expression

nvarchar(max)

NULL

tables_columns_expression_ja

tables_columns_expression_ja

nvarchar(max)

NULL

tables_columns_formatString

tables_columns_formatString

nvarchar(500)

NULL

tables_columns_isDataTypeInferred

tables_columns_isDataTypeInferred

bit

NULL

tables_columns_isHidden

tables_columns_isHidden

bit

NULL

tables_columns_isKey

tables_columns_isKey

bit

NULL

tables_columns_isNameInferred

tables_columns_isNameInferred

bit

NULL

tables_columns_isNullable

tables_columns_isNullable

bit

NULL

tables_columns_isUnique

tables_columns_isUnique

bit

NULL

tables_columns_keepUniqueRows

tables_columns_keepUniqueRows

bit

NULL

tables_columns_sortByColumn

tables_columns_sortByColumn

nvarchar(500)

NULL

tables_columns_sourceColumn

tables_columns_sourceColumn

nvarchar(500)

NULL

tables_columns_sourceProviderType

tables_columns_sourceProviderType

nvarchar(500)

NULL

tables_columns_summarizeBy

tables_columns_summarizeBy

nvarchar(500)

NULL

tables_columns_type

tables_columns_type

nvarchar(500)

NULL

sql_modules_definition

ssas.model_json_311_tables_columns - V script
/*
--get and check existing values

Select
    Distinct
    j2.[Key]
  , j2.Type
From
    ssas.model_json_31_tables                     As T1
    Cross Apply OpenJson ( T1.tables_columns_ja ) As j1
    Cross Apply OpenJson ( j1.Value ) As j2
Order by
    j2.[Key]
  , j2.Type
Go

Select
    T1.*
  , j2.*
From
    ssas.model_json_31_tables                     As T1
    Cross Apply OpenJson ( T1.tables_columns_ja ) As j1
    Cross Apply OpenJson ( j1.Value ) As j2
Go

Select
    j2.*
From
    ssas.model_json_31_tables                     As T1
    Cross Apply OpenJson ( T1.tables_columns_ja ) As j1
    Cross Apply OpenJson ( j1.Value ) As j2
Where
    j2.[Key] = 'description'

Select
    j2.*
From
    ssas.model_json_31_tables                     As T1
    Cross Apply OpenJson ( T1.tables_columns_ja ) As j1
    Cross Apply OpenJson ( j1.Value ) As j2
Where
    j2.[Key] = 'expression'
Go
*/
CREATE View ssas.model_json_311_tables_columns
As
Select
    T1.databasename
  , T1.tables_name
  , T1.RepoObject_guid
  , j2.tables_columns_name
  , j2.tables_columns_dataType
  , j2.tables_columns_description
  , j2.tables_columns_description_ja
  , j2.tables_columns_displayFolder
  , j2.tables_columns_expression
  , j2.tables_columns_expression_ja
  , j2.tables_columns_formatString
  , j2.tables_columns_isDataTypeInferred
  , j2.tables_columns_isHidden
  , j2.tables_columns_isKey
  , j2.tables_columns_isNameInferred
  , j2.tables_columns_isNullable
  , j2.tables_columns_isUnique
  , j2.tables_columns_keepUniqueRows
  , j2.tables_columns_sortByColumn
  , j2.tables_columns_sourceColumn
  , j2.tables_columns_sourceProviderType
  , j2.tables_columns_summarizeBy
  , j2.tables_columns_type
From
    ssas.model_json_31_tables_T                   As T1
    Cross Apply OpenJson ( T1.tables_columns_ja ) As j1
    Cross Apply
    OpenJson ( j1.Value )
    With
    (
        tables_columns_name NVarchar ( 128 ) N'$.name'
      , tables_columns_dataType NVarchar ( 500 ) N'$.dataType'
      , tables_columns_description NVarchar ( Max ) N'$.description'
      , tables_columns_description_ja NVarchar ( Max ) N'$.description' As Json --multiple lines?
      , tables_columns_displayFolder NVarchar ( 500 ) N'$.displayFolder'
      , tables_columns_expression NVarchar ( Max ) N'$.expression'
      , tables_columns_expression_ja NVarchar ( Max ) N'$.expression' As Json   --multiple lines?
      , tables_columns_formatString NVarchar ( 500 ) N'$.formatString'
      , tables_columns_isDataTypeInferred Bit N'$.isDataTypeInferred'
      , tables_columns_isHidden Bit N'$.isHidden'
      , tables_columns_isKey Bit N'$.isKey'
      , tables_columns_isNameInferred Bit N'$.isNameInferred'
      , tables_columns_isNullable Bit N'$.isNullable'
      , tables_columns_isUnique Bit N'$.isUnique'
      , tables_columns_keepUniqueRows Bit N'$.keepUniqueRows'
      , tables_columns_sortByColumn NVarchar ( 500 ) N'$.sortByColumn'
      , tables_columns_sourceColumn NVarchar ( 500 ) N'$.sourceColumn'
      , tables_columns_sourceProviderType NVarchar ( 500 ) N'$.sourceProviderType'
      , tables_columns_summarizeBy NVarchar ( 500 ) N'$.summarizeBy'
      , tables_columns_type NVarchar ( 500 ) N'$.type'
    ) As j2