All item definitions are stored in the definition column of the GDB_Items table. To identify which items are relationship classes, you must restrict your search to include only those items that are of a relationship class type. The information that identifies an item as a relationship class is stored in the name column of the GDB_ItemTypes table. You can join the two tables using the type column of the GDB_Items table (or GDB_Items_vw view in Oracle) and the UUID column of the GDB_ItemTypes table, then query for relationship class from the GDB_ItemTypes table.
The following examples query for the name of the relationship class and, if applicable, the feature dataset it is in plus the names of both tables or feature classes that participate in the relationship class. You might notice when querying your geodatabase that feature-linked annotation is also returned. That's because feature-linked annotation is a special type of relationship class.
--Queries a geodatabase in PostgreSQL
--Returns a list of relationship classes and their member tables
SELECT
(xpath('//CatalogPath/text()', definition))::text AS "Relationship class and dataset",
(xpath('//OriginClassNames/text()', definition))::text AS "Origin class",
(xpath('//DestinationClassNames/text()', definition))::text AS "Destination class"
FROM
sde.gdb_items items INNER JOIN sde.gdb_itemtypes itemtypes
ON items.type = itemtypes.uuid
WHERE
itemtypes.name = 'Relationship class';
--Queries a dbo-schema geodatabase in SQL Server
--Returns a list of relationship classes and their member tables
SELECT
ITEMS.Definition.value('(/DERelationshipClassInfo/CatalogPath)[1]', 'nvarchar(max)') AS "Relationship Class and dataset",
ITEMS.Definition.value('(/DERelationshipClassInfo/OriginClassNames/Name)[1]', 'nvarchar(max)') AS "Origin Class",
ITEMS.Definition.value('(/DERelationshipClassInfo/DestinationClassNames/Name)[1]', 'nvarchar(max)') AS "Destination Class"
FROM
dbo.GDB_ITEMS AS ITEMS INNER JOIN dbo.GDB_ITEMTYPES AS ITEMTYPES
ON ITEMS.Type = ITEMTYPES.UUID
WHERE
ITEMTYPES.Name = 'Relationship Class';
--Queries a geodatabase in Oracle
--Returns a list of relationship classes and their member tables
SELECT
EXTRACTVALUE(XMLType(Definition), '/DERelationshipClassInfo/CatalogPath') AS "Relationship Class and dataset",
EXTRACTVALUE(XMLType(Definition), '/DERelationshipClassInfo/OriginClassNames/Name') AS "Origin Class",
EXTRACTVALUE(XMLType(Definition), '/DERelationshipClassInfo/DestinationClassNames/Name') AS "Destination Class"
FROM
sde.GDB_ITEMS_VW ITEMS INNER JOIN sde.GDB_ITEMTYPES ITEMTYPES
ON ITEMS.Type = ITEMTYPES.UUID
WHERE
ITEMTYPES.Name = 'Relationship Class';
These queries return lists of the relationship class and the origin and destination classes that participate in the relationship class.
Relationship class and dataset Origin class Destination class ----------------------------------------- -------------------- ------------------------- mygdb.myuser.speciesrel mygdb.myuser.habitat mygdb.myuser.sptable mygdb.uruser.water\\mygdb.uruser.mainanno mygdb.uruser.mains mygdb.uruser.mdiam mygdb.hisuser.roads\\rowgrantrel mygdb.hisuser.row mygdb.hisuser.rowgrantors
The results indicate the geodatabase contains the following:
- A stand-alone relationship class named speciesrel in which the feature class habitat and table sptable participate
- A feature-linked annotation class in the feature dataset water and in which the mains and mdiam feature classes participate
- The relationship class rowgrantrel, which is in the roads feature dataset and is made up of the row and rowgrantors tables