After understanding the relationships between the four geodatabase system tables, the best way to see how they work together is to examine them using SQL. Selecting all rows and columns from the GDB_Items table returns a result set similar to the following (along with many other fields):
Values returned when querying the GDB_Items table
Notice that the values in the Type column are stored as UUIDs. As mentioned in An overview of the geodatabase system tables, these UUIDs can be resolved using the GDB_ItemTypes table, as shown in the following query:
SELECT gdb_items.UUID, gdb_itemtypes.name AS "Type", gdb_items.name
FROM gdb_items INNER JOIN gdb_itemtypes
ON gdb_items.type = gdb_itemtypes.UUID;
This returns a similar result as a simple SELECT statement run on the GDB_Items table, but with the UUIDs in the Type column replaced with human-readable strings:
Text values in the Type column
Similarly, the GDB_ItemRelationships table also contains UUIDs that are key values from other tables. Each relationship contains two of the UUID values seen in the GDB_Items table above: one for the relationship's destination item and one for its origin item. To resolve these to human-readable strings, you must join the GDB_ItemRelationships table to the GDB_Items table twice:
--SQL Server and PostgreSQL query
SELECT relationships.type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM gdb_items AS origin_items,
gdb_itemrelationships AS relationships,
gdb_items AS dest_items
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid;
--Oracle query
SELECT relationships.type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
sde.gdb_itemrelationships relationships,
sde.gdb_items dest_items
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid;
The illustration below is an example of a result set that could be returned from the previous query:
Text values for the origin and destination items names
Although this shows the relationships between items in the geodatabase, the relationship type is also needed. You can resolve the Type UUID by expanding the previous query to include a join with the GDB_ItemRelationshipTypes table:
--SQL Server and PostgreSQL query
SELECT reltypes.name AS type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM gdb_items AS origin_items,
gdb_itemrelationships AS relationships,
gdb_items AS dest_items,
gdb_itemrelationshiptypes AS reltypes
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid AND
relationships.type = reltypes.UUID;
--Oracle query
SELECT reltypes.name AS type,
origin_items.name AS "Origin Name", dest_items.name AS "Dest Name"
FROM sde.gdb_items origin_items,
sde.gdb_itemrelationships relationships,
sde.gdb_items dest_items,
sde.gdb_itemrelationshiptypes reltypes
WHERE
origin_items.UUID = relationships.originid AND
dest_items.UUID = relationships.destid AND
relationships.type = reltypes.UUID;
This returns the following:
Text values in the Type column from the GDB_ItemRelationshipTypes table