topic Re: How to find every reference to a particular domain in a geodatabase? in Data Management Questions

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

AndrewRudin — Wed, 12 Mar 2014 14:47:33 GMT

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

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

WilliamCraft — Wed, 12 Mar 2014 20:11:17 GMT

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.

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

AndrewRudin — Wed, 12 Mar 2014 20:19:15 GMT

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

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

WilliamCraft — Wed, 12 Mar 2014 20:32:44 GMT

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.

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

JakeSkinner — Sat, 11 Dec 2021 16:06:47 GMT

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

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

RobertBorchert — Thu, 13 Mar 2014 12:17:10 GMT

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

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

AndrewRudin — Thu, 13 Mar 2014 13:53:08 GMT

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.

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

AndrewRudin — Thu, 13 Mar 2014 13:59:46 GMT

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.

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

RobertBorchert — Thu, 13 Mar 2014 14:19:59 GMT

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.

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

GastonIzaguirre — Tue, 25 Nov 2014 16:08:11 GMT

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?

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

XanderBakker — Sat, 11 Dec 2021 16:06:50 GMT

I have used this in the past:

def main():
    import arcpy
    import os

    # output file (change this)
    report = r"D:\Xander\Domains\list_unused_domains.txt"

    # database connection (change this)
    conn = r"C:\Users\xbakker\AppData\Roaming\ESRI\Desktop10.2\ArcCatalog\Dev 10.2.sde"

    # set encoding to utf-8 (for non ASCII chars)
    import sys
    reload(sys)
    print sys.getdefaultencoding()
    def_enc = sys.getdefaultencoding()
    sys.setdefaultencoding('utf8')

    with open(report, "w") as f:
        f.write("Domains not in use\n")

        # create list of domains
        domains = arcpy.da.ListDomains(conn)
        lst_names = [domain.name for domain in domains]

        # list featureclasses
        arcpy.env.workspace = conn
        lst_fds = arcpy.ListDatasets(feature_type="feature")
        lst_fds.append("")
        lst_doms = []
        for fds in lst_fds:
            lst_fc = arcpy.ListFeatureClasses(feature_dataset=fds)
            for fc in lst_fc:
                try:
                    flds = arcpy.ListFields(os.path.join(ws, fds, fc))
                    for fld in flds:
                        if fld.domain != "":
                            lst_doms.append(fld.domain)
                except:
                    pass


        # process tables
        lst_tbl = arcpy.ListTables()
        for tbl in lst_tbl:
            try:
                flds = arcpy.ListFields(tbl)
                for fld in flds:
                    if fld.domain != "":
                        lst_doms.append(fld.domain)
            except:
                pass

         # create list of domains not in use
        lst_notinuse = list(set(lst_names) - set(lst_doms))

         # write to file
        for dom in lst_notinuse:
            f.write("{0}\n".format(dom))

    # restore default settings
    sys.setdefaultencoding(def_enc)


if __name__ == '__main__':
    main()

This does not get you the full list of domains that are in use. It is possible (and I have seen cases) where a column used with a default value in a subtype uses domain 'a' while the field itself uses domain 'b'. So if you have subtypes, you should check the domains in use by the subtypes too Use arcpy.da.ListSubtypes(featureclass).

Kind regards, Xander

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

GastonIzaguirre — Fri, 28 Nov 2014 15:12:04 GMT

Xander,

Thank you very much for your answer and for sharing your script code.

Unfortunately I'm using ArcGIS version 10, which does not contains the arcpy.da module, so I will try to adapt the script to make it compatible, some kind of backport, if possible.

Kind regards,

Gaston.

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

XanderBakker — Fri, 28 Nov 2014 15:18:40 GMT

I'm afraid there is not much support for listing domains without the data access (da) module. You may have to use one of the other suggestions (like the one by Jake) directly on the database.