Find fields for domain in SDE 10

Question

Hey

One of the things that I always look for is the fields that are associated with a domain.
This is needed if you need to delete the domain.
I tried to create a SQL that will give me this list and came out with this:

select name from  
(select OriginID from GDB_ITEMRELATIONSHIPS, GDB_ITEMS
where GDB_ITEMS.UUID = DestID and Name = 'mody') as FC_LIST
inner join
GDB_ITEMS as ITEMS
on ITEMS.UUID = FC_LIST.OriginID

(assuming "mody" is the domain).
I get the list of feature classes, is there any way to get the field names inside the feature classes?

Thanks
Mody

LauraWilson · Answer

This SQL returns the Domain XML as a table (SQL SERVER)
(we made it a function so we can reuse it)

CREATE FUNCTION [dbo].[GetDomainTable]
( 
@DomainName varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(

SELECT nInfo.Code, nInfo.Name
FROM
(
SELECT Convert(varchar(10), EachItem.query('data(Code)')) as ,
    Convert(varchar(100), EachItem.query('data(Name)')) as [Name]    
FROM
(
SELECT Definition FROM sde.GDB_ITEMS WHERE Name=@DomainName
)As SingleRow
CROSS APPLY
SingleRow.Definition.nodes('/GPCodedValueDomain2/CodedValues/CodedValue') as List(EachItem)
) As nInfo

)

To call the function
SELECT  *from MYTABLE a
 INNER JOIN [GetDomainTable] (
   'D_FREQUENCY') c on a.FREQUENCYID = c.Code

mody_buchbinder · Answer

Hey James

Thanks I will give it a try.
I have been and in the session in the Dev Summit and read the blog before asking my question.
The problem is with the detailed SQL of doing this task. As you can see from your answer �?? it is not simple!!

Many thanks
Mody

JamesMacKay · Answer

Hi Mody,

You can get the kind of detailed information you're looking for from the Definition column of the feature class rows in the GDB_Items table. The exact way to do this depends on your DBMS, due to differences in how XML columns are stored across different flavors of SDE, as well as the differences in XML functions across DBMSs. Another consideration is that domains can be associated with a class or with one or more specific subtypes of a class; two different XPaths are required for these two cases.

Here's an example of how to do this with SQL Server:DECLARE @DOMAIN_NAME NVARCHAR(MAX);
SET @DOMAIN_NAME = 'Material';

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

INSERT INTO @CLASS_DEFS
SELECT
 GDB_ITEMS.Name,
 GDB_ITEMS.Definition
FROM

-- Get the domain item's UUID.
((SELECT GDB_ITEMS.UUID AS UUID
 FROM GDB_ITEMS INNER JOIN GDB_ITEMTYPES
 ON GDB_ITEMS.Type = GDB_ITEMTYPES.UUID
 WHERE
 GDB_ITEMS.Name = @DOMAIN_NAME AND
 GDB_ITEMTYPES.Name IN ('Coded Value Domain', 'Range Domain')) AS Domain

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

-- Find the names of the origin items in the relationships.
INNER JOIN GDB_ITEMS
ON GDB_ITEMRELATIONSHIPS.OriginID = GDB_ITEMS.UUID;

-- 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

The Geodatabase blog has a handful of articles that have more information about working with the Definition column in 10.0 Geodatabases, here's an example of one:

http://blogs.esri.com/Dev/blogs/geodatabase/archive/2010/03/19/The-Simplified-Geodatabase-Schema-Part-3_3A00_-Querying-the-Definition-Field.aspx

Cheers,
James