Accessing differing geodatabase schemas from arcpy

Question

I am attempting to write a script that processes a lot of different feature classes in a geodatabase. Most of the feature classes have the same schema as the user connecting to the database

geodatabase.myschema.featureclass

however some feature classes have a schema belonging to a different user

geodatabase.differentschema.featureclass2

When using arcpy with a .sde connection and user that owns the default schema, I do not need to specify the schema name when referencing feature classes in arcpy tools.

import arcpy, os
db = r"D:\GIS\DBConns\geodatabase.sde"
fc = "featureclass"
fcpath = os.path.join(db, fc)

result = int(arcpy.GetCount_management(fcpath).getOutput(0))
print result

> 147

However if I reference the feature class with a different schema, the above doesn't work

Runtime error Traceback (most recent call last): File "<string">, line 6, in <module> 
File "C:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\management.py", line 15370, in GetCount
raise e Execute Error:
Failed to execute. Parameters are not valid.
Error 000732: Input Rows:
D:\GIS\DBConns\geodatabase.sde\featureclass2 does not exist or is not supported
Failed to execute (GetCount).

If I change the fc variable to include the schema name e.g. fc = "differentschema.featureclass2" then it works no problem.

Is there a way to quickly determine the schema name for a feature class from arcpy so that the tools work correctly?

I tried doing an arcpy.ListFeatureClasses("*featureclass2") however this adds a lot of time to the process (the gdb has a lot of feature classes, many of which are in Feature Datasets also), so would like to avoid that if possible.

Answer 1

You can always describe the SDE workspace and get all children recursively from the describe object. Faster than repeated calls to ListFC.

import arcpy
from pprint import pprint

gdb = r"C:\PostgreSQL.sde"

children = {}

# Walk the workspace and build dictionary of {name : catalog path}
# rpartition is perfect for extracting this information!
for item in arcpy.Describe(gdb).children:
    children[item.baseName.rpartition('.')[-1]] = item.catalogPath
    for grandchild in item.children:
        children[grandchild.baseName.rpartition('.')[-1]] = grandchild.catalogPath

pprint(children)

for fc in ['test', 'baz']:
    if fc in children:
        path = children[fc]
        count = arcpy.GetCount_management(path)[0]
        print("There are {} features in {}".format(count, path))


{'baz': 'C:\\PostgreSQL.sde\\foo.sde.baz',
 'myFD': 'C:\\PostgreSQL.sde\\foo.un.myFD',
 'test': 'C:\\PostgreSQL.sde\\foo.un.myFD\\foo.un.test'}
There are 0 features in C:\PostgreSQL.sde\foo.un.myFD\foo.un.test
There are 0 features in C:\PostgreSQL.sde\foo.sde.baz

Answer 2

Inspired by @Paul's answer (which worked but was still quite slow) I made an attempt which queried the database Table Registry sde_table_registry to get the list of tables.

import arcpy, os

gdb = r"D:\GIS\DBConns\geodatabase.sde"
arcpy.env.workspace = gdb

sqlconn = arcpy.ArcSDESQLExecute(gdb)

query = """ SELECT OWNER, TABLE_NAME FROM sde.SDE_TABLE_REGISTRY """

resultDict = {str(item[1]).upper(): str(item[0]).upper() for item in sqlconn.execute(query)}

for fc in ['featureclass', 'featureclass2']:
    if fc.upper() in resultDict:
        path = "{0}.{1}".format(resultDict[fc.upper()], fc)
        print path
        count = arcpy.GetCount_management(path)[0]
        print("There are {} features in {}".format(count, path))

Not an ideal way to do it, but much faster (4s vs 2m+). I did like @Paul's answer as it kept it within the arcpy/ArcGIS logic rather than stepping into SQL, but the time difference makes all the difference here!