topic Re: Delete Unused Domains in Python Questions

Delete Unused Domains

BlakeTerhune — Sat, 11 Dec 2021 07:57:07 GMT

I created this script to help with some geodatabase house cleaning tasks. Maybe someone else will find it useful or have an idea to improve it.

import arcpy
import os

# Set workspace
myGDB = r"C:\temp\working.gdb"

# Get domains that are assigned to a field
domains_used = []
for dirpath, dirnames, filenames in arcpy.da.Walk(myGDB, datatype=["FeatureClass", "Table"]):
    for filename in filenames:
        print "Checking {}".format(os.path.join(dirpath, filename))
        try:
            ## Check for normal field domains
            for field in arcpy.ListFields(os.path.join(dirpath, filename)):
                if field.domain:
                    domains_used.append(field.domain)
            ## Check for domains used in a subtype field
            subtypes = arcpy.da.ListSubtypes(os.path.join(dirpath, filename))
            for stcode, stdict in subtypes.iteritems():
                if stdict["SubtypeField"] != u'':
                    for field, fieldvals in stdict["FieldValues"].iteritems():
                        if not fieldvals[1] is None:
                            domains_used.append(fieldvals[1].name)
        except Exception, err:
            print "Error:", err

# Get domains that exist in the geodatabase
domains_existing = [dom.name for dom in arcpy.da.ListDomains(myGDB)]

# Find existing domains that are not assigned to a field
domains_unused = set(domains_existing) ^ set(domains_used)
print "{} unused domains in {}".format(len(domains_unused), myGDB)
for domain in domains_unused:
    arcpy.DeleteDomain_management(myGDB, domain)
    print "{} deleted".format(domain)

python snippets

Re: Delete Unused Domains

RuchiraWelikala — Sat, 11 Dec 2021 07:57:10 GMT

Hi Blake,

Thanks for the great script. A colleague recommended it to perform some long awaited maintenance on our SDE instances and this will be very handy.

While running it, however, the script through the following exception:

Traceback (most recent call last):
  File "P:\Scratch Workspace\CleanUnusedDomains.py", line 34, in <module>
    arcpy.DeleteDomain_management(myGDB, domain)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\management.py", line 1357, in DeleteDomain
    raise e
ExecuteError: ERROR 999999: Error executing function.
Must be the owner to perform this operation.
Failed to execute (DeleteDomain).

Is there a way to execute this code when the connection being used isn't the owner? Perhaps a way to skip over the domains that don't belong to the current owner.

Thanks again,

Ruch

Re: Delete Unused Domains

BlakeTerhune — Sat, 11 Dec 2021 07:57:12 GMT

That's a great point, Ruchira Welikala. I had only tested this with a file geodatabase, but the issue you are describing is absolutely correct with SDE; you need to be the owner. Conveniently, I've also been working on an SDE maintenance script that will reconcile versions, compress, rebuild indexes, and analyze datasets (source inspiration). For those last two tools, you need to run them as the data owner. My solution (for SDE in Oracle 11g):

Get all the distinct owner names from the domain objects
For each owner, create a temporary sde connection file.
Then use that temporary connection file to run whatever task you need (delete domain in this case).

Here is my final code that will remove unused domains from both local and remote (SDE) geodatabases. You will need to figure out a way to generate the password if it is different for each owner. Please test it out and let me know how it works for you.

import arcpy
from contextlib import contextmanager
import os
import shutil
import tempfile

def main():
    try:
        # Connection path to geodatabse (as administrator if SDE)
        myGDB = r"C:\GISConnections\SDE@GTEST.sde"

        # Get domains that are assigned to a field
        domainsUsed_names = []
        for dirpath, dirnames, filenames in arcpy.da.Walk(myGDB, datatype=["FeatureClass", "Table"]):
            for filename in filenames:
                print "Checking {}".format(os.path.join(dirpath, filename))
                ## Check for normal field domains
                for field in arcpy.ListFields(os.path.join(dirpath, filename)):
                    if field.domain:
                        domainsUsed_names.append(field.domain)
                ## Check for domains used in a subtype field
                subtypes = arcpy.da.ListSubtypes(os.path.join(dirpath, filename))
                for stcode, stdict in subtypes.iteritems():
                    if stdict["SubtypeField"] != u'':
                        for field, fieldvals in stdict["FieldValues"].iteritems():
                            if not fieldvals[1] is None:
                                domainsUsed_names.append(fieldvals[1].name)
                ## end for subtypes
            ## end for filenames
        ## end for geodatabase Walk

        # List of all existing domains (as domain objects)
        domainsExisting = arcpy.da.ListDomains(myGDB)

        # Find existing domain names that are not in use (using set difference)
        domainsUnused_names = (
            set([dom.name for dom in domainsExisting]) - set(domainsUsed_names)
        )

        # Get domain objects for unused domain names
        domainsUnused = [
            dom for dom in domainsExisting
            if dom.name in domainsUnused_names
        ]
        print "{} unused domains in {}".format(len(domainsUnused), myGDB)

        # Cleanup
        del domainsExisting
        del domainsUnused_names

        # Delete unused domains by owner
        ## For local geodatabses, owner is an empty string ('')
        with makeTempDir() as temp_dir:
            descGDB = arcpy.Describe(myGDB)
            for owner in set([dom.owner for dom in domainsUnused]):
                if descGDB.workspaceType == "RemoteDatabase":
                    ## Use temporary SDE connection as owner
                    myGDB = arcpy.CreateDatabaseConnection_management(
                        temp_dir,  ## out_folder_path
                        owner+".sde",  ## out_name
                        "ORACLE",  ## database_platform
                        "GISTEST.WORLD",  ## instance
                        "DATABASE_AUTH",  ## account_authentication
                        owner,  ## username
                        "myuserpass",  ## password
                    )
                    print arcpy.GetMessages()
                    ## Format result object as string for path to connection file
                    myGDB = str(myGDB)
                # Get unused domains for current owner
                domainsUnused_currentOwner = [
                    dom.name for dom in domainsUnused
                    if dom.owner == owner
                ]
                for domain in domainsUnused_currentOwner:
                    arcpy.DeleteDomain_management(myGDB, domain)
                    print "\t{} deleted".format(domain)
            ## end for domainsExisting_owners
        ## end with temp_dir

    finally:
        # Cleanup
        arcpy.ClearWorkspaceCache_management()


@contextmanager
def makeTempDir():
    """Creates a temporary folder and returns the full path name.
    Use in with statement to delete the folder and all contents on exit.
    Requires contextlib contextmanager, shutil, and tempfile modules.
    """
    temp_dir = tempfile.mkdtemp()
    try:
        yield temp_dir
    finally:
        shutil.rmtree(temp_dir)


if __name__ == '__main__':
    main()

Re: Delete Unused Domains

RuchiraWelikala — Fri, 14 Aug 2015 13:38:54 GMT

Wow, this is great stuff! Thanks, Blake. I will try out your script as well as your SDE maintenance scripts. The main problem with our SDE environment is that we have one production instance for both edits and map services. Hence, hundreds of locks that can't be broken to perform compressions and index rebuilds. I'm in the process of creating a dB replication workflow that'll likely remove some of the load off the instance and free up the database to allow for regular maintenance. Part of that is removing dozens of unused domains. Thanks again and I'll keep you posted. Cheers, Ruch

Re: Delete Unused Domains

LeonS — Fri, 14 Aug 2015 15:02:16 GMT

Thanks for sharing! I've had this script on my "to-do" list for longer than I can recall. May I suggest putting it up on GitHub for others to download and perhaps contribute?

Thanks again,

Leon

Re: Delete Unused Domains

BlakeTerhune — Fri, 14 Aug 2015 15:11:37 GMT

I've never tried using GitHub, but you're welcome to spread the love there if you like. Be sure to post a link if you do!

Re: Delete Unused Domains

BlakeTerhune — Fri, 14 Aug 2015 15:13:06 GMT

I think you can still delete unused domains regardless of the geodatabase locks that may exist. Worth a shot.

Re: Delete Unused Domains

RandyKreuziger — Fri, 14 Aug 2015 15:48:43 GMT

This is great. But I have a problem deleting Domains in SDE where I'm not the owner. The owner is long gone and I can't delete domains even tough I'm sa on the SQL instance! Anyone run into this on SDE?

Re: Delete Unused Domains

RuchiraWelikala — Fri, 14 Aug 2015 15:51:55 GMT

Oh yes, you can definitely delete with existing locks, however, deleting the unused domains is just one of the ways I'm trying to declutter our production environment. We're conducting an audit of all the items in the GDB. Deleting unused or archived feature classes/datasets/tables. Then I will deploy your script to remove the domains. My initial plan was to write a script that lists all of the unused domains and then go in manually and delete them myself, but this will save me hours of work! You're a life saver!

Re: Delete Unused Domains

BlakeTerhune — Fri, 14 Aug 2015 19:32:27 GMT

When you say the owner is long gone, do you mean the user account was deleted?

Re: Delete Unused Domains

RandyKreuziger — Fri, 14 Aug 2015 19:41:59 GMT

That is correct. Domains in SDE (Enterprise Database) are owned by the user who created them. A domain can be created explicated or gets created when the feature class with domains is copied into the SDE geodatabase. So, I have domains owned by kreuzrsk and some owned by and others owned by DBO. Since, I'm not DBO I can't delete those or any others ones I don't own.

Re: Delete Unused Domains

BlakeTerhune — Fri, 14 Aug 2015 20:39:24 GMT

You'll need to contact the people who can log in as those users to help you out. If they aren't around any more, you may be able to create the account again and use it to delete the domains owned by that person. If their account is still there but the person has left, just reset their password, connect with their account, complete your maintenance, then delete the account.

Re: Delete Unused Domains

LeonS — Mon, 17 Aug 2015 14:37:11 GMT

Here is the link to GitHub... Script on GitHub

I added an output file to list the unused domains and made delete an option. I was little nervous to just let it run, so I wanted to just see a list first.

Thanks again

Leon

Re: Delete Unused Domains

nizarsalih — Wed, 13 Jul 2016 21:21:34 GMT

Great tool, Thank you.

Re: Delete Unused Domains

RumikaChaudhry — Fri, 15 Dec 2017 21:15:48 GMT

This is an excellent script. I used it on an Enterprise SQL Geodatabase by only changing the db connection. Thanks Blake!

Re: Delete Unused Domains

HeenaLee — Tue, 16 Jan 2018 22:40:33 GMT

This is very helpful. Thank you for sharing!

I've used this script for ArcSDE. At first run, it deleted 14 unused domains. Then it showed me runtime error.

At second run, it deleted one unused domain. Then I've encountered the same runtime error again. See below error message.

Runtime error Traceback (most recent call last): File "<string>", line 34, in <module> File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\management.py", line 1462, in DeleteDomain raise e ExecuteError: ERROR 999999: Error executing function. The domain is used as a default domain. Failed to execute (DeleteDomain).

Do you have any idea what's wrong? Thank you in advance!

Re: Delete Unused Domains

RandyKreuziger — Tue, 16 Jan 2018 22:53:46 GMT

Hi Heena,

A domain you're trying to delete can't be deleted as long as it is a domain still in use. You'll have to identify which feature class and/or table fields are set to use that domain. Then delete that association before you run the delete.

Re: Delete Unused Domains

BlakeTerhune — Wed, 17 Jan 2018 15:15:32 GMT

According to Esri documentation, it does indeed mean the domain is already used somewhere. It also means that there's some case where the code is getting a false positive that the domain is unused or else it shouldn't be trying to delete it. If you comment out the line that attempts to delete the domain

arcpy.DeleteDomain_management(myGDB, domain)‍

it will just print the domain that should be deleted. It's possible that domain is used in a subtype and the code is somehow working incorrectly in that case. I may not have fully tested with subtypes.

Re: Delete Unused Domains

HeenaLee — Wed, 17 Jan 2018 16:30:47 GMT

After commenting out "arcpy.DeleteDomain_management(myGDB, domain)", the script has found the rest of domains that are not used. This helps a lot. Thank you for your help Blake and Randy!

Re: Delete Unused Domains

AndresCastillo — Sat, 11 Dec 2021 07:57:15 GMT

Based off your original post, how would I modify the script so that it can be converted to a tool, with the input being any user defined sde database, and the output being a message with the 'domains_unused'

Here's what I have so far.....it works as a tool, but I do not see the print messages being output under geoprocessing results or some sort of text file message:

import arcpy  
import os  

#Slightly modified from Geonet thread by Blake Terhune titled 'Delete Unused Domains'  
# Set workspace  
arcpy.env.workspace = arcpy.GetParameterAsText(0)
 
  
# Get domains that are assigned to a field  
domains_used = []  
for dirpath, dirnames, filenames in arcpy.da.Walk(myGDB, datatype=["FeatureClass", "Table"]):  
    for filename in filenames:  
        print "Checking {}".format(os.path.join(dirpath, filename))  
        try:  
            ## Check for normal field domains  
            for field in arcpy.ListFields(os.path.join(dirpath, filename)):  
                if field.domain:  
                    domains_used.append(field.domain)  
            ## Check for domains used in a subtype field  
            subtypes = arcpy.da.ListSubtypes(os.path.join(dirpath, filename))  
            for stcode, stdict in subtypes.iteritems():  
                if stdict["SubtypeField"] != u'':  
                    for field, fieldvals in stdict["FieldValues"].iteritems():  
                        if not fieldvals[1] is None:  
                            domains_used.append(fieldvals[1].name)  
        except Exception, err:  
            print "Error:", err  
  
# Get domains that exist in the geodatabase  
domains_existing = [dom.name for dom in arcpy.da.ListDomains(myGDB)]  
  
# Find existing domains that are not assigned to a field  
domains_unused = set(domains_existing) ^ set(domains_used)  
print "{} unused domains in {}".format(len(domains_unused), myGDB) 
print domains_unused
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍