Browsing 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 a UUID. As mentioned in A quick tour 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 UUID 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:

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

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:

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

This returns the following: