Identify where domains have been applied in a geodatabase

For ArcSDE for Oracle, the following optional commands will make the output more readable in SQLPlus:

Code:
column Object_Owner format a15
column Domain_Owner format a15
column Domain_Name format a15
column Object_Name format a15
column Field_Name format a15
column Subtype_Name format a15

The following queries can be run in either SQLPlus for Oracle or Query Analyzer for SQL Server:

Query to list domains applied at the feature class level:

Code:
SELECT DO.owner Domain_Owner, FI.defaultdomainname Domain_Name,OC.owner Object_Owner,OC.name Object_Name,FI.fieldname Field_Name
FROM sde.gdb_objectclasses OC,sde.gdb_fieldinfo FI, sde.gdb_domains DO 
WHERE OC.id=FI.classid AND DO.domainname=FI.defaultdomainname 
AND FI.defaultdomainname IS NOT NULL
ORDER BY Domain_Owner, Domain_Name,Object_Owner,Object_Name

Query to list domains associated at the subtype level:

Code:
SELECT DM.owner Domain_Owner,DM.domainname Domain_Name,OC.name Object_Name,ST.subtypename Subtype_Name,AR.fieldname Field_Name 
FROM sde.gdb_domains DM,sde.gdb_objectclasses OC,sde.gdb_subtypes ST,sde.gdb_attrrules AR,sde.gdb_validrules VR
WHERE (AR.domainname=DM.domainname) AND (AR.ruleid=VR.id) AND (VR.classid = OC.id) AND(AR.subtype=ST.subtypecode) 
ORDER BY Domain_Owner,Domain_Name,Object_Name,Subtype_Name

Note:
If these SQL queries are in SQLPlus, be sure to add a ";" to the end of each statement.