3

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.

3
  • 2
    Is there any particular reason why you wouldn't want to use arcpy for that, iterating your datasets with Python and listing field domains? Nov 1, 2016 at 17:10
  • Good question. I want to use the list to ultimately use PL/SQL to loop through all tables, all fields with CV domains, and validate all values against their associated domains (errors happen due to field calculations, etc.). And yes, this too can be done with python easily. I've chosen to try to do it with SQL and PL/SQL for a few reasons: 1) It is a massive learning opportunity (man, have I learned a lot about geodatabases, system tables, XML, SQL, PL/SQL).
    – User1974
    Nov 1, 2016 at 17:27
  • 2) From my experience, python/ArcPy is slow. I want to check my entire geodatabase often, make corrections, refresh views on the fly, and not wait very long to do so. I think SQL will be good for this. 3) I have been inspired by this post which says 'Your database code will always outlive your application client technology.'. I want to create a sustainable solution, that will not be forgotten. I'm hoping to store it in a VIEW in the database.
    – User1974
    Nov 1, 2016 at 17:27

3 Answers 3

5

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.
0
1

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
0
1

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
1
  • Does this join have a purpose? JOIN GDB_ITEMTYPES it ON i.Type = it.UUID I don't see it referenced anywhere in the query. If that's the case, I wonder if it could be removed.
    – User1974
    Jun 26, 2022 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.