Example: Returning a list of relationship classes

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