SQL Domains – List All Fields and Domains for Each Table Using SQL

domainsenterprise-geodatabasegeodatabase-xmloracle-dbmssql

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.

Best Answer

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.
Related Question