sql get list of domains and the tables that use them

Solutions on MaxInterview for sql get list of domains and the tables that use them by the best coders in the world

showing results for - "sql get list of domains and the tables that use them"
Julia
13 Sep 2019
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