How to find every reference to a particular domain in a geodatabase?

AndrewRudin · ‎03-12-2014

Does anyone know how to use python to find every field (and fields on subtypes) that use a particular domain? Our SDE is 10.2 and on an Oracle database, and I have desktop 10.2.1. Basically if I need to change a domain (like change it's data type from string to integer) I need to delete it and recreate it. In order to delete it I need to find every place it's used and un-reference it. This is hard to do in a large SDE where domains are shared between many fields and feature classes.

The out of the box method seems to be looping through EVERY feature class(and subtype) on the database and running arcpy.describe on every field see if they use a particular domain. This seems extremely inefficient and will take forever on our large SDE. Surely there must be a better way.

I figured querying the SDE administration tables would be quicker but ran into some walls. By querying the GDB_Items and GDB_ItemRelationship tables I was able to see what feature classes reference a domain, but these don't seem to tell you which field(s) are using the domain, nor does it seems to show if a particular subtype uses a domain.

I was curious how SDE at 10.2 actually stores that a field is using domain x, and also how it tracks that subtype y uses domain x. This way I can query the administration tables directly to make this process much faster.

I plan to use the proper toolbox tools to make the domain assignment changes, I just want to query the administrative tables to find the references and quickly as possible.

Thank you for any help you can provide,

Andrew

WilliamCraft · ‎03-12-2014

Can you give this SQL script a try in your Oracle database (run as SDE, SYSTEM, OR SYS user)?

[ATTACH=CONFIG]32159[/ATTACH]

The script, when run in a 10.1 or higher Oracle geodatabase, should return all domains as well as which feature classes to which they are associated.

Let me know if it works.

AndrewRudin · ‎03-12-2014

Thanks for the code. It does run, but it doesn't provide the name of the field the domain is used for, or the field on a subtype within a feature class that uses a domain. This does help though since it reduces the number of feature classes I would have to loop through.

Thanks again.

Andrew

WilliamCraft · ‎03-12-2014

I'm sure there's a Python solution that would be more complete in terms of the detail you originally asked to have. I couldn't find any way to link the domain to a field within the ArcSDE system tables at 10.1 or higher. Glad to hear this is somewhat helpful, though. I'll see what I can dig up in terms of Python scripting. Lastly, it would be nice if you could vote up helpful posts when people assist you in the forums.

JakeSkinner · ‎03-13-2014

Hi Andrew,

You can use the python extension cx_Oracle to run a SQL query to find which feature classes have a domain applied. You can then loop through each feature class and find which fields have a domain applied. Here is an example:

import cx_Oracle, arcpy
from arcpy import env

connstr = 'sde/sde@orcl'
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()

list = []

#find feature classes that contain a domain and append to list
curs.execute("select UUID from sde.gdb_items where type = '{8C368B12-A12E-4C7E-9638-C9C64E69E98F}'")
for row in curs:
    curs2 = conn.cursor()
    curs2.execute("select ORIGINID from SDE.gdb_itemrelationships where DESTID = '" + row[0] + "'")
    for row2 in curs2:
        curs3 = conn.cursor()
        curs3.execute("select NAME from sde.gdb_items where UUID = '" + row2[0] + "'")
        for row3 in curs3:
            list.append(row3[0])

conn.close()

#remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

env.workspace = r"Database Connections\VECTOR@ORCL.sde"

#loop through fields to find which has a domain
for fc in list:
    for field in arcpy.ListFields(fc):
        if field.domain:
            print fc, field.name

RobertBorchert · ‎03-13-2014

Use the GIS Diagrammer

http://kiwigis.blogspot.com/2010/07/arcgis-diagrammer-for-arcgis-10.html

Does anyone know how to use python to find every field (and fields on subtypes) that use a particular domain? Our SDE is 10.2 and on an Oracle database, and I have desktop 10.2.1. Basically if I need to change a domain (like change it's data type from string to integer) I need to delete it and recreate it. In order to delete it I need to find every place it's used and un-reference it. This is hard to do in a large SDE where domains are shared between many fields and feature classes.

The out of the box method seems to be looping through EVERY feature class(and subtype) on the database and running arcpy.describe on every field see if they use a particular domain. This seems extremely inefficient and will take forever on our large SDE. Surely there must be a better way.

I figured querying the SDE administration tables would be quicker but ran into some walls. By querying the GDB_Items and GDB_ItemRelationship tables I was able to see what feature classes reference a domain, but these don't seem to tell you which field(s) are using the domain, nor does it seems to show if a particular subtype uses a domain.

I was curious how SDE at 10.2 actually stores that a field is using domain x, and also how it tracks that subtype y uses domain x. This way I can query the administration tables directly to make this process much faster.

I plan to use the proper toolbox tools to make the domain assignment changes, I just want to query the administrative tables to find the references and quickly as possible.

Thank you for any help you can provide,

Andrew

AndrewRudin · ‎03-13-2014

Exploring this made me curious how ESRI actually stores what fields use domains. I guess its stored in some XML blob somewhere now at 10.1.

AndrewRudin · ‎03-13-2014

Thank you for the code JSkinn3,

I have used the arcpy.ArcSDESQLExecute command in the past to return sql results. http://resources.arcgis.com/en/help/main/10.2/#/ArcSDESQLExecute/018z0000007z000000/

I'll have to check out that extension since sometimes managing how arcpy returns records can be squirrely.

RobertBorchert · ‎03-13-2014

To go the unorthodox route.

You can export the XML Schema of a geodatabase. Do Schema only NOT data. gets large that way

Open that XML in Excel. copy and paste it to notepad. copy and paste it back into excel. Some fields will be hidden under the moniker *metadata when you open it in excel. putting it in notepad removes it and makes it visible.

Now import that excel file into Access and now assess the imported table. There should be a column called DomainName and that will contain all your Domains. Most of the entries will be blank.

Every feature class will be shown in this table and when I tested it out there was a column called DomainName48 that contained the the name of a domain and the name of the feature in a another column and the name of the attribute it was assigned to.

However, when I tested this out I did it on a database that had a number of Subtyped features. It will show you every instance of every Subtype of that feature that uses that domain.

Conversely you can use the file in Excel itself when you past it back in and using the Filter you can filter it to show a single domain name and then it will show you all the features (and subtypes) with that domain

It isn't pretty but it works.

Exploring this made me curious how ESRI actually stores what fields use domains. I guess its stored in some XML blob somewhere now at 10.1.

GastonIzaguirre · ‎11-25-2014

Is there a tool or script to list all active domains (and field names in each feature where are used) and all unused domains in a workspace?