Relationship queries

A common reason you would query the geodatabase system tables is to find all the geodatabase items related to a dataset. 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 geodatabase replica. Since all items and relationships are stored in the same system tables, a single query can be used for all these cases by providing string literals to designate which item to query. 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 in a geodatabase in Microsoft SQL Server. To change the 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, the feature dataset may contain some items that don't appear in ArcCatalog, such as the dirty area feature classes used by topologies.

Although this works well for relationships such as datasets in feature dataset and feature classes in topology, a slight modification is needed when you want to find the origin items of relationships based on a specific destination item. Examples of this type of relationship navigation include finding which datasets use a specific domain, determining to which geodatabase replica a replica dataset belongs, or identifying the controller datasets in which 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