sql get list of domains and the tables that use them

1--Queries an sde-schema geodatabase in SQL Server
2
3DECLARE @DOMAIN_NAME NVARCHAR(MAX);
4SET @DOMAIN_NAME = 'Material';
5
6DECLARE @CLASS_DEFS TABLE
7(
8     Name nvarchar(max),
9     Definition XML
10)
11
12--Insert records to temporary record set
13INSERT INTO @CLASS_DEFS
14SELECT
15  sde.gdb_items.Name,
16  sde.gdb_items.Definition
17FROM
18-- Get the domain item's UUID.
19((SELECT GDB_ITEMS.UUID AS UUID
20  FROM sde.gdb_items INNER JOIN sde.gdb_itemtypes
21  ON sde.gdb_items.Type = sde.gdb_itemtypes.UUID
22  WHERE
23    sde.gdb_items.Name = @DOMAIN_NAME AND
24    sde.gdb_itemtypes.Name IN ('Coded Value Domain','Range Domain')) AS Domain
25
26-- Find the relationships with the domain as the DestinationID.
27INNER JOIN sde.gdb_itemrelationships
28ON Domain.UUID = sde.gdb_itemrelationships.DestID)
29
30-- Find the names of the origin items in the relationships.
31INNER JOIN sde.gdb_items
32ON Domain.UUID = sde.gdb_itemrelationships.DestID
33
34-- Extract the field definitions.
35SELECT
36  ClassDefs.Name AS "Class Name",
37  fieldDef.value('Name[1]', 'nvarchar(max)') AS "Field Name",
38  NULL AS "Subtype Name"
39FROM
40  @CLASS_DEFS AS ClassDefs
41CROSS APPLY
42  Definition.nodes('/*/GPFieldInfoExs/GPFieldInfoEx') AS FieldDefs(fieldDef)
43WHERE
44  fieldDef.value('DomainName[1]', 'nvarchar(max)') = @DOMAIN_NAME
45
46UNION
47
48SELECT
49  ClassDefs.Name AS "Class Name",
50  fieldDef.value('FieldName[1]', 'nvarchar(max)') AS "Field Name",
51  fieldDef.value('(../../SubtypeName)[1]', 'nvarchar(max)') AS "Subtype Name"
52FROM
53  @CLASS_DEFS AS ClassDefs
54CROSS APPLY
55   Definition.nodes('/*/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo') AS FieldDefs(fieldDef)
56WHERE
57  fieldDef.value('DomainName[1]', 'nvarchar(max)') = @DOMAIN_NAME
58