I have an Oracle Enterprise geodatabase with many feature classes. I would like a SQL query to find all the polygon feature classes with only one record in its table. So far, I can only query all the feature classes.
SELECT * FROM SDE.GDB_ITEMS items INNER JOIN SDE.GDB_ITEMTYPES itemtypes ON items.Type = itemtypes.UUID WHERE itemtypes.Name = 'Feature Class' Several parts are involved:
where clause to include only those tables that are of feature class type and their shape type are of polygon (parsing XML is involved).This code would work on SQL Server:
SELECT TableName = t.NAME, TableSchema = s.Name, RowCounts = p.rows FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id WHERE t.name in ( (SELECT PARSENAME(items.Name,1) FROM SDE.GDB_ITEMS items INNER JOIN SDE.GDB_ITEMTYPES itemtypes ON items.Type = itemtypes.UUID WHERE itemtypes.Name = 'Feature Class' and Definition.value('(/DEFeatureClassInfo/ShapeType)[1]', 'varchar(max)') = 'esriGeometryPolygon')) and t.is_ms_shipped = 0 and p.rows = 1 GROUP BY t.NAME, s.Name, p.Rows ORDER BY s.Name, t.Name The results:
TableName TableSchema RowCounts -- ------------------ ------------- ----------- 0 ND_1226_DIRTYAREAS dbo 1 1 REDLINING dbo 1 2 SS_WORKAREAS dbo 1 3 T_1_DIRTYAREAS dbo 1 
parsename - regexp_substr() and how you parse the XML in Oracle - stackoverflow.com/questions/12982687/…