Example: Discovering domain usage using SQL

Relationship queries discussed how to use the GDB_ItemRelationships table to discover which tables and feature classes are using a particular domain for validation. It is possible to find more detailed information, however, such as to which field the domain is applied and which subtypes use it (if subtypes exist). The following steps outline one approach to finding this information:

1. Insert the names and definitions of all items with relationships to a specified domain into a temporary record set.
2. Extract the names of the fields for all items where the domain is used at the class level (/*/GPFieldInfoExs/GPFieldInfoEx/DomainName).
3. Extract the names of the fields and subtypes for all items where the domain is used at the subtype level (/*/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo/DomainName).
4. Union these two result sets.

The following example queries a geodatabase in SQL Server for a domain named Material.

--Queries an sde-schema geodatabase in SQL Server

DECLARE @DOMAIN_NAME NVARCHAR(MAX);
SET @DOMAIN_NAME = 'Material';

DECLARE @CLASS_DEFS TABLE
(
     Name nvarchar(max),
     Definition XML
)

--Insert records to temporary record set
INSERT INTO @CLASS_DEFS
SELECT
  sde.gdb_items.Name,
  sde.gdb_items.Definition
FROM
-- Get the domain item's UUID.
((SELECT GDB_ITEMS.UUID AS UUID
  FROM sde.gdb_items INNER JOIN sde.gdb_itemtypes
  ON sde.gdb_items.Type = sde.gdb_itemtypes.UUID
  WHERE
    sde.gdb_items.Name = @DOMAIN_NAME AND
    sde.gdb_itemtypes.Name IN ('Coded Value Domain','Range Domain')) AS Domain

-- Find the relationships with the domain as the DestinationID.
INNER JOIN sde.gdb_itemrelationships
ON Domain.UUID = sde.gdb_itemrelationships.DestID)

-- Find the names of the origin items in the relationships.
INNER JOIN sde.gdb_items
ON Domain.UUID = sde.gdb_itemrelationships.DestID

-- Extract the field definitions.
SELECT
  ClassDefs.Name AS "Class Name",
  fieldDef.value('Name[1]', 'nvarchar(max)') AS "Field Name",
  NULL AS "Subtype Name"
FROM
  @CLASS_DEFS AS ClassDefs
CROSS APPLY
  Definition.nodes('/*/GPFieldInfoExs/GPFieldInfoEx') AS FieldDefs(fieldDef)
WHERE
  fieldDef.value('DomainName[1]', 'nvarchar(max)') = @DOMAIN_NAME

UNION

SELECT
  ClassDefs.Name AS "Class Name",
  fieldDef.value('FieldName[1]', 'nvarchar(max)') AS "Field Name",
  fieldDef.value('(../../SubtypeName)[1]', 'nvarchar(max)') AS "Subtype Name"
FROM
  @CLASS_DEFS AS ClassDefs
CROSS APPLY
   Definition.nodes('/*/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo') AS FieldDefs(fieldDef)
WHERE
  fieldDef.value('DomainName[1]', 'nvarchar(max)') = @DOMAIN_NAME

This example queries a geodatabase in Oracle for a domain named AncillaryRoleDomain:

--Queries a geodatabase in Oracle

CREATE TABLE CLASS_DEFS  
(
	name varchar2(32),
	definition XMLType
);

--Insert records to temporary record set
INSERT INTO CLASS_DEFS
SELECT
  sde.gdb_items_vw.Name,
  XMLType(sde.gdb_items_vw.Definition)
FROM(
	(
		-- Get the domain item's UUID.
		SELECT GDB_ITEMS_VW.UUID AS UUID
		FROM sde.gdb_items_vw INNER JOIN sde.gdb_itemtypes
			ON sde.gdb_items_vw.Type = sde.gdb_itemtypes.UUID
		WHERE	sde.gdb_items_vw.Name = 'AncillaryRoleDomain' AND
			sde.gdb_itemtypes.Name IN ('Coded Value Domain','Range Domain')
    	) Domain
	-- Find the relationships with the domain as the DestinationID.
	INNER JOIN sde.gdb_itemrelationships
		ON Domain.UUID = sde.gdb_itemrelationships.DestID
) 
-- Find the names of the origin items in the relationships.
INNER JOIN sde.gdb_items_vw
	ON sde.gdb_items_vw.UUID = sde.gdb_itemrelationships.OriginID;

-- Extract the field definitions.
SELECT 	CLASS_DEFS.Name AS "Class Name",
	EXTRACTVALUE(fields.Column_Value, '/GPFieldInfoEx/Name') AS "Field Name",
	null AS "Subtype Name" 
FROM CLASS_DEFS, 
	TABLE(XMLSEQUENCE(Extract(CLASS_DEFS.definition, '/*/GPFieldInfoExs/GPFieldInfoEx'))) fields
UNION
SELECT table_name AS "Class Name",
	EXTRACTVALUE(subtypes_fields.Column_value, '/SubtypeFieldInfo/FieldName') as column_name,
	subtype_name AS "Subtype Name"
FROM	(	
		SELECT 	CLASS_DEFS.Name AS table_name,
			subtypes.COLUMN_VALUE XMLVal,
			EXTRACTVALUE(subtypes.COLUMN_VALUE, '/Subtype/SubtypeName') AS subtype_name,
			EXTRACTVALUE(subtypes.COLUMN_VALUE, '/Subtype/SubtypeCode') AS subtype_value
		FROM	CLASS_DEFS, 
			TABLE(XMLSEQUENCE(Extract(CLASS_DEFS.definition, '/DEFeatureClassInfo/Subtypes/Subtype'))) subtypes
	) subtypes_fields,
	TABLE(XMLSEQUENCE(subtypes_fields.XMLVal.Extract('/Subtype/FieldInfos/SubtypeFieldInfo'))) subtypes_fields;
  
DROP TABLE CLASS_DEFS;