List of numeric default values using SQL

Question

Oracle 18c 10.7.1 EGDB:

---

I have fields in feature classes and tables that have numeric default values.

How can I get a list of the default values using SQL?

I believe the default values are stored in geodatabase system tables. The geodatabase default values are not to be confused with Oracle database default values.

The fields I want in the query:

• OBJECT_NAME
• COLUMN_NAME
• NUMERIC_DEFAULT_VALUE
• OBJECT_TYPE

The output would look like this:

---

Related:

• Select domain codes/descriptions using XMLTABLE instead of EXTRACTVALUE
• Select subtype and a subtype field's domain names using SQL

Answer 1

I can use Oracle's xmltable function to extract XML values from the definition column in gdb_items_vw.

There is a different XML path for feature classes vs. tables. So we need separate queries for feature classes and tables. And then union the queries together.

The queries only get numeric default values. I believe textual default values would use a different XML tag than /DefaultValueNumeric.

select      
    i.name          as object_name,
    x.column_name   as column_name,
    x.default_value as numeric_default_value,
    'Table'         as object_type
from        
    sde.gdb_items_vw i
cross apply xmltable(
    '/DETableInfo/GPFieldInfoExs/GPFieldInfoEx' 
    passing xmltype(i.definition)
    columns
        column_name   varchar2(50)  path './Name',
        default_value varchar2(255) path './DefaultValueNumeric'
    ) x    
where      
    i.name is not null
    and x.default_value is not null
    and i.name like 'INFRASTR.%'

union all

select      
    i.name          as object_name,
    x.column_name   as column_name,
    default_value   as numeric_default_value,
    'Feature Class' as object_type
from        
    sde.gdb_items_vw i
cross apply xmltable(
    '/DEFeatureClassInfo/GPFieldInfoExs/GPFieldInfoEx' 
    passing xmltype(i.definition)
    columns
        column_name   varchar2(50)  path './Name',
        default_value varchar2(255) path './DefaultValueNumeric'
    ) x    
where      
    i.name is not null
    and x.default_value is not null
    and i.name like 'INFRASTR.%'
order by
    object_name

---

Edit:

As @John pointed out, I forgot that subtype fields have their own default values. So, the queries below handle that case: get the subtype field default values.

Notes:

• It looks like the query gets both numeric and textual default values (unlike the non-subtype queries above).
• The queries could be unioned with the above non-subtype queries. But I suppose the fields would need to be changed first so that they align.

select 
    i.name as table_name,
    x.subtype_code,
    x.subtype_description,
    x.subtype_field,
--  x.subtype_field_domain,
    x.subtype_field_default_value
from   
    sde.gdb_items_vw i
cross apply xmltable(
    '/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo'--[FieldName="ACTIVITY"]'
    passing xmltype(i.definition)
    columns
        subtype_code                number(38,0)  path './../../SubtypeCode',
        subtype_description         varchar2(255) path './../../SubtypeName',
        subtype_field               varchar2(255) path './FieldName',
--      subtype_field_domain        varchar2(255) path './DomainName',
        subtype_field_default_value varchar2(255) path './DefaultValue'
    ) x
where  
    i.name is not null
    and i.name like 'INFRASTR.%'
    and x.subtype_field_default_value is not null

union all

select 
    i.name as table_name,
    x.subtype_code,
    x.subtype_description,
    x.subtype_field,
--  x.subtype_field_domain,
    x.subtype_field_default_value
from   
    sde.gdb_items_vw i
cross apply xmltable(
    '/DEFeatureClassInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo'--[FieldName="ACTIVITY"]'
    passing xmltype(i.definition)
    columns
        subtype_code                number(38,0)  path './../../SubtypeCode',
        subtype_description         varchar2(255) path './../../SubtypeName',
        subtype_field               varchar2(255) path './FieldName',
--      subtype_field_domain        varchar2(255) path './DomainName',
        subtype_field_default_value varchar2(255) path './DefaultValue'
    ) x
where  
    i.name is not null
    and i.name like 'INFRASTR.%'
    and x.subtype_field_default_value is not null

Answer 2

Let me preface this by saying, this is not a pure SQL solution. However, if you're having difficulty isolating correct XML tags and xml locations for all the tables, feature classes, and potentially other datasets to get all default values, you could also look into using arcpy (ArcMap, ArcServer, or ArcPro python modules) and walk a database (https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/walk.htm) and for each relevant dataset, use List Fields (https://pro.arcgis.com/en/pro-app/latest/arcpy/functions/listfields.htm) to get field objects (https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/field.htm) with various details, including all of the requested info. You could then write out that data to a CSV or to a table of your choice that could then be accessed via SQL. Again, not a perfect solution for your request, but it could give you all defaults for all field types in all datasets at one time and in one output if you so desired.

Also, as an FYI, this (and likely your solution as well) assumes Subtypes are not being used. If you have subtypes enabled in one of your datasets, each subtype can store its own field defaults, which I'm almost positive are stored separately from the standard field defaults. I'm not sure about their location in the back-end system tables, but they can also be accessed via python. Use List Subtypes (https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/listsubtypes.htm). I will warn you though, the documentation is very lacking for this. My suggestion, initially, would be to just use arcpy.da.ListSubtypes(FullPathToDataset) on a dataset that has subtypes, print the entire resulting dictionary, and get a sense for it before you try writing the actual code to extract the desired info. It's pretty straight-forward, but it can take a little sorting through to understand the subtype dictionary.