🔍
GeneXus ドメイン一覧をKB用データベースから取得する方法
記事投稿経緯
2023.02.17 Zennへ記載サイト移行
2022.07.13 Qiitaへ投稿
本記事について
GeneXusのIDE上からでもドメイン一覧は確認できるものの
「ドメイン一覧をExcelに出したい」「Enumを使っているものを調べたい」などといった場合には
ドメインを1つ1つ見なければならない。その手間を省くためのSQLをご紹介します。
ドメインだけでなく、オブジェクトや項目属性もID値を変えれば出せるので
ここから拡張して使いやすくできるかと思いますのでご活用ください。
ドメイン一覧取得クエリ(17U2-10)
コードを表示する
ドメイン一覧取得クエリ(17U2)
SELECT
ROW_NUMBER() OVER (ORDER BY [ドメインID]) AS 'No'
, TBL.*
FROM
(
SELECT
EV.EntityVersionName AS 'ドメインID'
, EV.EntityVersionDescription AS 'ドメイン名'
, D.DataType AS '項目型'
, L.Length AS '桁数'
, CASE
WHEN E.EnumValues IS NOT NULL
THEN 'False'
ELSE '-'
END AS 'Empty'
, ISNULL(E.EnumValues, '') AS 'パラメータ群（EnumValues）'
FROM
EntityVersion AS EV
INNER JOIN (
SELECT
EntityTypeId
, EntityId
, MAX(EntityVersionId) AS EntityVersionId
FROM
EntityVersion
WHERE
EntityTypeId = 27 -- Domain 26⇒27
GROUP BY
EntityTypeId
, EntityId
) AS TMP
ON EV.EntityTypeId = TMP.EntityTypeId
AND EV.EntityId = TMP.EntityId
AND EV.EntityVersionId = TMP.EntityVersionId -- 各項目をLEFT JOINするのは、項目によって属性を持たないことがあり、縦横変換(ROW_NUMBER～OVER～PARTITION BY～) が利用できない為。
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId,ModelEntityPropertyId
, CASE
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>12</DataType></AttCustomType>'
THEN 'Datetime'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>4</DataType></AttCustomType>'
THEN 'Numeric'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>5</DataType></AttCustomType>'
THEN 'Character'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>8</DataType></AttCustomType>'
THEN 'LongVarchar'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>13</DataType></AttCustomType>'
THEN 'Varchar'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>15</DataType></AttCustomType>'
THEN 'Boolean'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>6</DataType></AttCustomType>'
THEN 'Date'
ELSE ModelEntityPropertyValue
END AS DataType
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
ModelId = 1
AND EntityTypeId = 27 -- Domain 26⇒27
AND ModelEntityPropertyId = 25 -- 型 24⇒25
) AS D
ON EV.EntityTypeId = D.EntityTypeId
AND EV.EntityId = D.EntityId
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
ModelEntityPropertyValue
, '<EnumValues><Values>'
, ''
)
, '</Values></EnumValues>'
, ''
)
, '<EnumValue>'
, ''
)
, '</EnumValue>'
, ''
)
, '<Name>'
, 'Name:'
)
, '</Name>'
, ''
)
, '<Description>'
, ',Description:'
)
, '</Description>'
, ','
)
, '<Value>'
, 'Value:'
)
, '</Value>'
, '$CHAR10$'
)
, char (10)
, ''
)
, char (13)
, ''
)
, '$CHAR10$'
, char (13) + char (10)
) AS EnumValues
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
ModelId = 1
AND EntityTypeId = 27 -- Domain 26⇒27
AND ModelEntityPropertyId = 26 -- EnumValues 25⇒26
) AS E
ON EV.EntityTypeId = E.EntityTypeId
AND EV.EntityId = E.EntityId
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, ModelEntityPropertyValue AS Length
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
ModelId = 1
AND EntityTypeId = 27 -- Domain 26⇒27
AND ModelEntityPropertyId = 10 -- 桁 26⇒10
) AS L
ON EV.EntityTypeId = L.EntityTypeId
AND EV.EntityId = L.EntityId
) AS TBL
ORDER BY
[ドメインID]
ドメイン一覧取得クエリ(16U4)
コードを表示する
ドメイン一覧取得クエリ(16U4)
SELECT
ROW_NUMBER() OVER (ORDER BY [ドメインID]) AS 'No'
, TBL.*
FROM
(
SELECT
EV.EntityVersionName AS 'ドメインID'
, EV.EntityVersionDescription AS 'ドメイン名'
, D.DataType AS '項目型'
, L.Length AS '桁数'
, CASE
WHEN E.EnumValues IS NOT NULL
THEN 'False'
ELSE '-'
END AS 'Empty'
, ISNULL(E.EnumValues, '') AS 'パラメータ群（EnumValues）'
FROM
EntityVersion AS EV
INNER JOIN (
SELECT
EntityTypeId
, EntityId
, MAX(EntityVersionId) AS EntityVersionId
FROM
EntityVersion
WHERE
EntityTypeId = 26
GROUP BY
EntityTypeId
, EntityId
) AS TMP
ON EV.EntityTypeId = TMP.EntityTypeId
AND EV.EntityId = TMP.EntityId
AND EV.EntityVersionId = TMP.EntityVersionId -- 各項目をLEFT JOINするのは、項目によって属性を持たないことがあり、縦横変換(ROW_NUMBER～OVER～PARTITION BY～) が利用できない為。
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, CASE
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>12</DataType></AttCustomType>'
THEN 'Datetime'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>4</DataType></AttCustomType>'
THEN 'Numeric'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>5</DataType></AttCustomType>'
THEN 'Character'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>8</DataType></AttCustomType>'
THEN 'LongVarchar'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>13</DataType></AttCustomType>'
THEN 'Varchar'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>15</DataType></AttCustomType>'
THEN 'Boolean'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>6</DataType></AttCustomType>'
THEN 'Date'
ELSE ModelEntityPropertyValue
END AS DataType
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
ModelId = 1
AND EntityTypeId = 26 -- Domain
AND ModelEntityPropertyId = 24 -- 型
) AS D
ON EV.EntityTypeId = D.EntityTypeId
AND EV.EntityId = D.EntityId
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
ModelEntityPropertyValue
, '<EnumValues><Values>'
, ''
)
, '</Values></EnumValues>'
, ''
)
, '<EnumValue>'
, 'Enum:'
)
, '</EnumValue>'
, ''
)
, '<Name>'
, 'Name:'
)
, '</Name>'
, ''
)
, '<Description>'
, ',Description:'
)
, '</Description>'
, ','
)
, '<Value>'
, 'Value:'
)
, '</Value>'
, ','
)
, char (10)
, ''
)
, char (13)
, ''
) AS EnumValues
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
ModelId = 1
AND EntityTypeId = 26 -- Domain
AND ModelEntityPropertyId = 25 -- EnumValues
) AS E
ON EV.EntityTypeId = E.EntityTypeId
AND EV.EntityId = E.EntityId
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, ModelEntityPropertyValue AS Length
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
ModelId = 1
AND EntityTypeId = 26 -- Domain
AND ModelEntityPropertyId = 26 -- 桁
) AS L
ON EV.EntityTypeId = L.EntityTypeId
AND EV.EntityId = L.EntityId
) AS TBL
ORDER BY
[ドメインID]
ドメイン一覧取得クエリ(15U12)
コードを表示する
ドメイン一覧取得クエリ(15U12)
SELECT
ROW_NUMBER() OVER (ORDER BY [ドメインID]) AS 'No'
, TBL.*
FROM
(
SELECT DISTINCT
EV.EntityVersionName AS 'ドメインID'
, EV.EntityVersionDescription AS 'ドメイン名'
, D.DataType AS '項目型'
, L.Length AS '桁数'
, ISNULL(E.EnumValues, '') AS 'パラメータ群（EnumValues）'
FROM
EntityVersion AS EV
INNER JOIN (
SELECT
EntityTypeId
, EntityId
, MAX(EntityVersionId) AS EntityVersionId
FROM
EntityVersion
WHERE
EntityTypeId = 26 -- 種別:ドメイン
GROUP BY
EntityTypeId
, EntityId
) AS TMP
ON EV.EntityTypeId = TMP.EntityTypeId
AND EV.EntityId = TMP.EntityId
AND EV.EntityVersionId = TMP.EntityVersionId --INNER JOIN (
-- SELECT
-- EntityVersionName
-- , max(EntityVersionId) as EntityVersionId
-- FROM EntityVersion
-- WHERE EntityTypeId = 26 -- 種別:ドメイン
-- GROUP BY EntityVersionName
--) AS TMP
--ON EV.EntityVersionName = TMP.EntityVersionName
--AND EV.EntityVersionId = TMP.EntityVersionId
-- 各項目をLEFT JOINするのは、項目によって属性を持たないことがあり、縦横変換(ROW_NUMBER～OVER～PARTITION BY～) が利用できない為。
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, CASE
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>12</DataType></AttCustomType>'
THEN 'Datetime'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>4</DataType></AttCustomType>'
THEN 'Numeric'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>5</DataType></AttCustomType>'
THEN 'Character'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>8</DataType></AttCustomType>'
THEN 'LongVarchar'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>13</DataType></AttCustomType>'
THEN 'Varchar'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>15</DataType></AttCustomType>'
THEN 'Boolean'
WHEN ModelEntityPropertyValue = '<AttCustomType><DataType>6</DataType></AttCustomType>'
THEN 'Date'
ELSE ModelEntityPropertyValue
END AS DataType
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
EntityTypeId = 26 -- 種別:ドメイン
AND ModelEntityPropertyId = 20 -- 型
) AS D
ON EV.EntityTypeId = D.EntityTypeId
AND EV.EntityId = D.EntityId
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
REPLACE (
ModelEntityPropertyValue
, '<EnumValues><Values>'
, ''
)
, '</Values></EnumValues>'
, ''
)
, '<EnumValue>'
, 'Enum:'
)
, '</EnumValue>'
, ''
)
, '<Name>'
, 'Name:'
)
, '</Name>'
, ''
)
, '<Description>'
, ',Description:'
)
, '</Description>'
, ','
)
, '<Value>'
, 'Value:'
)
, '</Value>'
, ','
)
, char (10)
, ''
)
, char (13)
, ''
) AS EnumValues
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
EntityTypeId = 26 -- 種別:ドメイン
AND ModelEntityPropertyId = 21 -- EnumValues
) AS E
ON EV.EntityTypeId = E.EntityTypeId
AND EV.EntityId = E.EntityId
LEFT OUTER JOIN (
SELECT
EntityTypeId
, EntityId
, ModelEntityPropertyValue AS Length
FROM
ModelEntityProperty WITH (NOLOCK)
WHERE
EntityTypeId = 26 -- 種別:ドメイン
AND ModelEntityPropertyId = 22 -- 桁
) AS L
ON EV.EntityTypeId = L.EntityTypeId
AND EV.EntityId = L.EntityId
) AS TBL
ORDER BY
[ドメインID]
最後に...
数少ない「GeneXus」エンジニアへ有益な情報になりますように。
Discussion