While updating an older Python script used to rebuild indexes in an Enterprise Geodatabase, we ran into a familiar challenge: reliably identifying database views when iterating through feature classes.
Historically, we handled this by enforcing a naming convention. Views were suffixed with “_vw” or “_view”, which allowed them to be filtered out using simple string logic.
# set the workspace environment
arcpy.env.workspace = gdbpath
# Create a list of all the feature classes.
fclist = arcpy.ListFeatureClasses()
# fclist
# Confirm we have a feature class, append if so.
for fc in fclist:
print("Checking", fc)
fcname = fc.split(".")[-1].lower() # split the fc name to get the last part for comparing against the view.
if not fcname.endswith(("_view", "_vw")):
print("Adding", fc, "to list...")
# append the list of feature classes to main list to be analyzied.
objectlist.append(fc)
While this approach works, it relies on two assumptions:
- Naming standards are consistently followed.
- The script will only ever be run against databases that follow those standards.
Both assumptions tend to break down over time, especially when scripts are reused across environments or inherited by other teams.
A More Reliable Approach: Query the Database Catalog
A more robust solution is to let the database itself tell us which objects are views. Using arcpy.ArcSDESQLExecute(), we can query the system catalogs directly and retrieve an authoritative list of views, independent of naming conventions.
SQL Server Example:
# Get a list of all the view in the database
gdbconn = arcpy.ArcSDESQLExecute(gdbpath)
sql = r"""
SELECT v.name AS view_name
FROM sys.views v
JOIN sys.schemas s
ON v.schema_id = s.schema_id
WHERE s.name NOT IN ('sys', 'INFORMATION_SCHEMA', 'sde');
"""
runsql = gdbconn.execute(sql)
# Since the data is returned as a list of lists lets convert to a simple list.
viewlist = [row[0] for row in runsql]
viewlist
PostgreSQL (PostGIS) Example:
# Get a list of all the view in the PostGIS database
gdbconn = arcpy.ArcSDESQLExecute(postgispath)
sql = r"""
SELECT viewname AS view_name
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'sde');
"""
runsql = gdbconn.execute(sql)
# Since the data is returned as a list of lists lets convert to a simple list.
viewlist = [row[0] for row in runsql]
viewlist
Note: During testing, we observed that ArcPy now appears to handle views more gracefully than it has in the past. Operations that previously failed when run against views now complete successfully. However, scripts still take significantly longer when views are included, making it beneficial to explicitly filter them out.
How This is Better
By querying the database catalog directly, maintenance scripts can:
- Avoid reliance on naming conventions
- Work consistently across environments
- Safely exclude spatial views that ArcPy exposes as feature classes
This approach results in more resilient automation by removing assumptions that are prone to human error. It is particularly effective for index maintenance, statistics updates, and bulk schema operations in Enterprise Geodatabases.
If you’re dealing with Enterprise Geodatabase maintenance or automation challenges, reach out to Spatialty. This is the type of work we focus on: building reliable, repeatable GIS workflows that hold up across environments.
Count Feature Class Attachments with SQL
The post describes the creation of an ArcGIS Dashboard in ArcGIS Enterprise to track data collection progress on field assets,…
Subtypes and Domains Descriptions Query
Esri has some documentation that shows how to extract the descriptions of coded value domains and subtypes with an SQL…
GIS Data Cataloger
A Python tool designed to catalog and document geographic information system (GIS) data within a specified workspace, leveraging the capabilities…
You Shall Not Delete!
The Enterprise Geodatabase uses an Attribute Rule to prevent deletion of features with assigned IDs from an external database. Using…
Something went wrong. Please refresh the page and/or try again.
Spatialty 