A common goal when working with the geodatabase system tables is to find all the items related to a specific item. Examples of this include finding the contents of a feature dataset, finding which feature classes participate in a topology or a geometric network, and finding which datasets are included in a replica. Since all items and relationships are stored in the same tables, a single query can be used for all these cases (with string literals determining which specific item will be used). The following steps can be used to perform this query:
- Find the UUID of the origin item (such as a feature dataset).
- Find all relationships with the UUID as the origin UUID.
- For each destination UUID, find the corresponding item and resolve its type using the GDB_ItemTypes table.
The following is a SQL query incorporating all these steps. To change the specific item being queried, modify both of the string variables declared at the beginning.
--Queries a dbo-schema geodatabase in SQL Server DECLARE @ORIGIN nvarchar(max); DECLARE @ORIGIN_TYPE nvarchar(max); SET @ORIGIN = 'Montgomery.Sasha.Landbase'; SET @ORIGIN_TYPE = 'Feature Dataset'; SELECT DEST_ITEMS.Name AS "Name" DEST_TYPES.Name AS "Type" FROM -- Get the unique ID of the origin item. ((((SELECT UUID, Type FROM dbo.GDB_ITEMS WHERE Name = @ORIGIN) AS src_items INNER JOIN (SELECT UUID FROM dbo.GDB_ITEMTYPES WHERE Name = @ORIGIN_TYPE) AS src_types ON src_items.Type = src_types.UUID) --Get the UUIDs of related items. INNER JOIN dbo.GDB_ITEMRELATIONSHIPS AS relationships ON src_items.UUID = relationships.OriginID) -- Resolve the names of the destination items. INNER JOIN dbo.GDB_ITEMS AS dest_items ON relationships.DestID = dest_items.UUID) -- Get the types as human-readable strings. INNER JOIN dbo.GDB_ITEMTYPES AS dest_types ON dest_items.Type = dest_types.UUID
In the case of a feature dataset, this returns a list similar to what you would see when browsing a feature dataset in ArcCatalog; it could contain feature classes; relationship classes; and controller datasets such as topologies, network datasets, and parcel fabrics. Additionally, it may contain some datasets that don't appear in ArcCatalog such as the dirty area feature classes used by topologies.
Although this works well for relationships such as dataset in feature dataset and feature class in topology, a slight modification is needed when trying to find the origin items of relationships based on a specific destination item. This kind of relationship navigation can be used to find which datasets use a specific domain, to which replica a replica dataset belongs, or in what controller memberships a feature class participates. The following is a modified version of the previous query, altered so that related items can be found by destination item rather than origin item:
--Queries a dbo-schema geodatabase in SQL Server DECLARE @DEST nvarchar(max); DECLARE @DEST_TYPE nvarchar(max); SET @DEST = 'Angle'; SET @DEST_TYPE = 'Range Domain'; SELECT SRC_ITEMS.Name AS "Name", SRC_TYPES.Name AS "Type" FROM -- Get the unique ID of the destination item. ((((SELECT UUID, Type FROM dbo.GDB_ITEMS WHERE Name = @DEST) AS dest_items INNER JOIN (SELECT UUID FROM dbo.GDB_ITEMTYPES WHERE Name = @DEST_TYPE) AS dest_types ON dest_items.Type = dest_types.UUID) -- Get the UUIDs of related items. INNER JOIN dbo.GDB_ITEMRELATIONSHIPS AS relationships ON dest_items.UUID = relationships.DestID) -- Resolve the names of the origin items. INNER JOIN dbo.GDB_ITEMS AS src_items ON relationships.OriginID = src_items.UUID) -- Get the types as human-readable strings. INNER JOIN dbo.GDB_ITEMTYPES AS src_types ON src_items.Type = src_types.UUID
For information on the system tables and views used in Oracle, see the "XML in geodatabase system tables" section of A quick tour of the geodatabase system tables.