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.

• Alex Tereshenkov: Is there any particular reason why you wouldn't want to use arcpy for that, iterating your datasets with Python and listing field domains?
• User1974: 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: 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.

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

• User1974: 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.