List of all fields and their domains for each table using SQL (table name, field name, domain name)

Question

I'm trying to generate a list of all fields (in all tables in an Oracle geodatabase) and the coded value domains that they use.

+------------+------------+-------------+
| table_name | field_name | domain_name |
+------------+------------+-------------+
| table_1    | field_1    | domain_A    |
| table_1    | field_2    | null        |
| table_1    | field_3    | domain_B    |
| table_1    | field_4    | domain_B    |
+------------+------------+-------------+
| table_2    | field_1    | null        |
| table_2    | field_2    | domain_C    |
| table_2    | field_3    | domain_A    |
+------------+------------+-------------+

I've tried using the third example on this page: Query geodatabase system tables with SQL. It gets me the table names and the associated domains. But it's missing the field name:

SELECT reltypes.name AS type,
   origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
   sde.gdb_itemrelationships relationships,
   sde.gdb_items dest_items,
   sde.gdb_itemrelationshiptypes reltypes
WHERE
   origin_items.UUID = relationships.originid AND
   dest_items.UUID = relationships.destid AND
   relationships.type = reltypes.UUID AND
   reltypes.name = 'DomainInDataset' AND
   origin_items.name LIKE 'PUBWORKS.%'
ORDER BY  origin_items.name

How can I make a list of all fields and their domains, for each table using SQL? Keep in mind that a domain can be used by more than one field, per table (or even by multiple tables).

Environment is Oracle 12c 10.3.1 Geodatabase.

Answer 1

Refer to the XML in the DEFINITION field in SDE.GDB_ITEMS_VW.

select
    i.name as table_name,
    extractvalue(definition_xml.column_value, 'GPFieldInfoEx/Name') as field_name,
    extractvalue(definition_xml.column_value, 'GPFieldInfoEx/DomainName') as domain_name,
    it.name as object_type
from        
     sde.gdb_items_vw i 
inner join sde.gdb_itemtypes it 
    on i.type = it.uuid
cross join 
    xmlsequence(xmltype(definition).extract('/DETableInfo/GPFieldInfoExs/GPFieldInfoEx')) definition_xml
where        
    i.name is not null 
    and extractvalue(definition_xml.column_value, 'GPFieldInfoEx/DomainName') is not null 
order by
    i.name

Notes:

• This query is only designed to get domains for non-spatial tables. To get domains for feature classes, change /DETableInfo to /DEFeatureClassInfo.
• The string literals are case-sensitive. Example: GPFieldInfoEx/DomainName works, but gpfieldinfoex/domainname wouldn't work. It wouldn't throw an error, but the values would be null.
• The performance of the query could likely be improved by creating a materialized view on sde.gdb_items that uses the same logic as sde.gdb_items_vw (and basing the domain query on the materialized view).
• It's also possible to extract other XML objects like a subtype's codes, descriptions, fields, and domains.

Answer 2

I used this syntax for SQL and it worked.

SELECT
    i.NAME AS item_name
    ,xVal.value('Name[1]', 'nvarchar(max)') field_name
    ,xVal.value('DomainName[1]', 'nvarchar(max)') domain_name 
    ,it.NAME AS item_type
FROM        
     SDE.GDB_ITEMS  i 
JOIN SDE.GDB_ITEMTYPES it 
ON 
         i.Type = it.UUID
    CROSS APPLY i.Definition.nodes('/DETableInfo/GPFieldInfoExs/GPFieldInfoEx') dx(xVal)
WHERE        
    i.NAME IS NOT NULL AND
    xVal.value('DomainName[1]', 'nvarchar(max)') IS NOT NULL 
ORDER BY
    i.NAME

Answer 3

Modified from Jaime Burgos. The CROSS APPLY line had the parameter for Oracle. It is different in SQL as included below.

SELECT
    i.Name AS FeatureClass
    ,xVal.value('Name[1]','nvarchar(max)') Field
    ,xVal.value('DomainName[1]', 'nvarchar(max)') Domain
FROM GDB_ITEMS  i JOIN GDB_ITEMTYPES it
    ON i.Type = it.UUID
CROSS APPLY i.Definition.nodes('/*/GPFieldInfoExs/GPFieldInfoEx') dx(xVal)
WHERE i.NAME IS NOT NULL
    AND xVal.value('DomainName[1]', 'nvarchar(max)') IS NOT NULL
ORDER BY i.NAME