Relationship queries

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.