Zenn
🔍

GeneXus ドメイン一覧をKB用データベースから取得する方法

2023/02/17に公開
genexus
tech
記事投稿経緯

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