ssas.model_json_351_roles_members - V
type: V ( view ), modify_date: 2021-09-01 07:36:49
RepoObject_guid: 0060D8EE-E90A-EC11-8516-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
1 |
|
|
||
2 |
|
|
||
3 |
|
|
||
|
|
Indexes
PK_model_json_351_roles_members
-
IndexSemanticGroup: no_group
-
databasename; nvarchar(128)
-
[column-roles_name]; nvarchar(500)
-
[column-roles_members_memberId]; nvarchar(500)
-
-
PK, Unique, Real: 1, 1, 0
idx_model_json_351_roles_members__2
-
IndexSemanticGroup: no_group
-
databasename; nvarchar(128)
-
[column-roles_name]; nvarchar(500)
-
-
PK, Unique, Real: 0, 0, 0
idx_model_json_351_roles_members__3
-
IndexSemanticGroup: no_group
-
databasename; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
sql_modules_definition
ssas.model_json_351_roles_members - V script
/*
--get and check existing values
Select
Distinct
j2.[Key]
, j2.Type
From
ssas.model_json_35_roles As T1
Cross Apply OpenJson ( T1.roles_members_ja ) As j1
Cross Apply OpenJson ( j1.Value ) As j2
ORDER BY
j2.[Key]
, j2.Type
Go
Select
T1.*
, j2.*
From
ssas.model_json_35_roles As T1
Cross Apply OpenJson ( T1.roles_members_ja ) As j1
Cross Apply OpenJson ( j1.Value ) As j2
Go
Select
DISTINCT
j2.*
From
ssas.model_json_35_roles As T1
Cross Apply OpenJson ( T1.roles_members_ja ) As j1
Cross Apply OpenJson ( j1.Value ) As j2
Where
j2.[Key] = 'memberName'
GO
*/
Create View ssas.model_json_351_roles_members
As
Select
T1.databasename
, T1.roles_name
, j2.roles_members_memberId
, j2.roles_members_memberName
From
ssas.model_json_35_roles As T1
Cross Apply OpenJson ( T1.roles_members_ja ) As j1
Cross Apply
OpenJson ( j1.Value )
With
(
roles_members_memberId NVarchar ( 500 ) N'$.memberId'
, roles_members_memberName NVarchar ( 500 ) N'$.memberName'
) As j2
sql