Report on the geometry storage type used for all spatial data in an Enterprise Geodatabase using SQL

Summary

All geodatabases store geometry (hence the 'geo' in geodatabase). Each DBMS product used for enterprise geodatabases provides specific geometry storage mechanisms. Oracle, SQL Server, and PostgreSQL DBMSs allow the choice of different geometry storage options.

For SQL Server databases, Microsoft's geometry or geography spatial types can be used. For Oracle databases, either ST_Geometry, or Oracle Spatial, and for PostgreSQL, you can use either the ST_Geometry or the PostGIS geometry type.

The following geometry storage types have been deprecated in ArcGIS Pro:

• SDEBINARY
• SDELOB
• WKB_GEOMETRY

You may view data with these geometry storage types, but new data cannot be created using these storage types.

Organizations may require to report on or obtain a list of the geometry storage type used for each feature class in an enterprise geodatabase. This list can be used to, for example, migrate all feature classes using SDEBINARY to a geometry storage type supported with ArcGIS Pro and the DBMS.

Procedure

When connecting to an enterprise geodatabase from an ArcGIS client, you interact with the datasets that you or other databases users have added to the geodatabase. To track that data and to implement geodatabase behavior, enterprise geodatabases use system tables.

The 'SDE_layers' system table stores a record for each feature class registered with the geodatabase. The table has a field called 'layer_config' which indicates the configuration keyword used by the feature class. The configuration keyword parameters tell the database how to store the data in the database.

DBTUNE is a table in the geodatabase repository that stores configuration keywords and parameters. Each parameter is record in the table, and each configuration keyword is associated with one or more records. The keyword parameter 'GEOMETRY_STORAGE' specifies the geometry storage type used by the feature class. 

Using a SQL query, you can query the 'SDE_layers' and 'SDE_DBTUNE' system tables to obtain the geometry storage type used for each feature class in the geodatabase.

An example of the SQL query that can be executed:

SELECT lyr.table_name, lyr.layer_config, dbtune.parameter_name, dbtune.config_string
FROM sde.SDE_layers as lyr
JOIN sde.SDE_dbtune dbtune
ON lyr.layer_config = dbtune.keyword
WHERE dbtune.parameter_name = 'GEOMETRY_STORAGE'

The output from the query:

GEOMTEST      GEOGRAPHY GEOMETRY_STORAGE GEOGRAPHY
GEOMTEST2     GEOMETRY  GEOMETRY_STORAGE GEOMETRY

Note:
If you are using a dbo owned schema for the geodatabase system tables, modify the above query to use dbo to access the tables.