Query geodatabase system tables with SQL

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):

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:

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:

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: