Listing Database Views

When using ListTables() or ListFeatureClasses() with an enterprise geodatabase, the returned list will include the database views. In many cases, users name views with a unique prefix or suffix for distinction (i.e. VW_PARCELOWNERS or PARCELOWNERS_VW). For views with such a naming convention, you can get a list of them by using a list comprehension:

arcpy.env.workspace = r"c:\data\CityOfRedlands.sde"
views = [v for v in arcpy.ListTables() if v.endswith("_VW")]

If views have no specific naming convention or you want to ensure all the views are returned, you can use the ArcSDESQLExecute class.

For SQL Server:

sql_execute = arcpy.ArcSDESQLExecute(r'Database Connections\SQLSERVERDB.sde')
views = sql_execute.execute("select name from sys.objects where type = 'V'")

For Oracle:

sql_execute = arcpy.ArcSDESQLExecute(r'Database Connections\MYORACLEDB.sde')
# List user views
views = sql_execute.execute("select view_name from user_views")

For other database types, be sure to provide the correct SQL expression.

Advertisements

4 thoughts on “Listing Database Views

  1. Is there a way to know what database you are using at 10.2???? I know you can use the describe object to find out if it’s SDE, FGDB, PGDB, etc.., but finding the actual underlying database has been a problem for me for quite sometime. It’s hard to write generic functions when it comes to datetime and things like listing views as demonstrated above.

    • Hi Drew, have a look at the Code sample at the bottom of this page. It shows how to get most of the properties
      http://resources.arcgis.com/en/help/main/10.2/index.html#/Workspace_properties/018v0000002v000000/

      >>> d = arcpy.Describe(r’Database Connections\Connection to omaha.sde’)
      >>> props = d.connectionProperties
      >>> print(‘\n’.join(‘{0}={1}’.format(i,getattr(props, i)) for i in [‘SERVER’, ‘INSTANCE’, ‘DBCLIENT’, ‘DB_CONNECTION_PROPERTIES’, ‘DATABASE’, ‘USER’, ‘AUTHENTICATION_MODE’ ]))
      SERVER=omaha
      INSTANCE=sde:sqlserver:omaha
      DBCLIENT=sqlserver
      DB_CONNECTION_PROPERTIES=omaha
      DATABASE=winniedb
      USER=tomb
      AUTHENTICATION_MODE=DBMS

  2. Agreed. Big “thanks”. For anyone who scrolls down.. it prints stuff like [u’Yadda_Yadda_etc’]. If you use the following it’ll give you cleaner output:

    for v in views:
    print str(v[0])

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s