topic Re: ListTables returns Database Synonyms and ListFields Does not recognize them in Python Questions

ListTables returns Database Synonyms and ListFields Does not recognize them

JakeMatthys — Sat, 11 Dec 2021 09:59:33 GMT

I am working in an enterprise SDE database. I am working with a particular schema that contains tables, views and synonyms that have and have not been registered with the SDE and the Geodatabase. Ultimately, I am trying to retrieve domains that are being used in the schema, but I have ran into an issue along the way.

The schema contains database synonyms. I have found that arcpy.ListTables will return the database synonym, but arcpy.ListFields will not recognize the database synonym. I would like to exclude the synonyms from my process, but arcpy.ListTables does not provide a utility to filter the return set by object type (info and dbase do not work in this case).

Here is my specific example which I have abbreviated to highlight the issue. The MYSCHEMA.RESERVOIR object is a database synonym:

tableList = arcpy.ListTables()
for table in tableList:    
   if "MYSCHEMA." in table:
       if "RESERVOIR" in table:
            print table
            fieldList = arcpy.ListFields(table) ## code breaks here
            print "made it here"
            for field in fieldList:
                 print table

MYSCHEMA.RESERVOIR


Traceback (most recent call last):
  File "<pyshell#71>", line 6, in <module>
    fieldList = arcpy.ListFields(table)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\__init__.py", line 1119, in ListFields
    return gp.listFields(dataset, wild_card, field_type)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\geoprocessing\_base.py", line 344, in listFields
    self._gp.ListFields(*gp_fixargs(args, True)))
IOError: "MYSCHEMA.RESERVOIR" does not exist

So it appears that ListFields will not recognize a database synonym. Does anyone have any thoughts about how to filter out database synonyms before running arcpy.ListFields? I would prefer not to filter out the synonym objects by name, as these names could change and that would require someone to update the code.

Thanks in advance.

- Jake

Re: ListTables returns Database Synonyms and ListFields Does not recognize them

ChristianWells — Sat, 11 Dec 2021 09:59:36 GMT

Hi Jake,

You could try describing the table prior to the list fields. Once the object is described, look for the dataset ID (DSID) property. Anything not registered with the geodatabase will return -1. If the value doesn't equal or is greater than -1, perform the arcpy.ListFields() operation.

>>> tblList = arcpy.ListTables()
>>> for tbl in tblList:
...     desc = arcpy.Describe(tbl)
...     print desc.DSID
...     
-1

Re: ListTables returns Database Synonyms and ListFields Does not recognize them

JakeMatthys — Sat, 11 Dec 2021 09:59:39 GMT

Hi Christian,

Thanks for the response, but that didn't work.

Here is my example. I've included 3 different types of database objects. I have a registered table (CUSTOMER_INFO), I have a non-registered view (SP_XY) and a synonym (RESERVOIR). The registered table and non-registered view return expected results, but the synonym is not recognized as a table object by arcpy.describe, therefore it returns an error.

tableList = arcpy.ListTables()
for table in tableList:      
        if "MYSCHEMA." in table:  
            if "RESERVOIR" in table or "SP_XY" in table or "CUSTOMER_INFO" in table:  
                print table
                desc = arcpy.Describe(table)
                print desc.dsid


                
MYSCHEMA.CUSTOMER_INFO
100384
MYSCHEMA.SP_XY
-1
MYSCHEMA.RESERVOIR


Traceback (most recent call last):
  File "<pyshell#1>", line 5, in <module>
    desc = arcpy.Describe(table)
  File
Traceback (most recent call last):
  File "<pyshell#1>", line 5, in <module>
    desc = arcpy.Describe(table)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\__init__.py", line 1234, in Describe
    return gp.describe(value)
  File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\geoprocessing\_base.py", line 374, in describe
    self._gp.Describe(*gp_fixargs(args, True)))
IOError: "MYSCHEMA.RESERVOIR" does not exist
 "C:\Program Files (x86)\ArcGIS\Desk





top10.2\arcpy\arcpy\__init__.py", line 1234, in Descri

Thanks for the suggestion.

- Jake