we are migrating from a legacy Oracle database to a SQL database and as such need to ensure the data in both the source and target systems migrate in full.
in the target database we have various tables that hold valuetypes of items that are held as numbers rather than the full description of the type.
Q> Does anyone know where the subtype description may be held in the database as I can not seem to find them in the specific table.
in the example I will post via a screenshot I am selecting a type of valve in the valve table
I have this SQL query that might be relevant for what you are trying to do- if nothing else I think it will allow you to modify it depending upon if you're interested in associated domains and if domains are used in conjunction with subtypes (looks like they are from your screenshot). As you suggested- subtypes and domains are largely managed / stored within the gdb_items and gdb_itemtypes tables (if your geodatabase is dbo schema just change table owner schema from sde. to dbo.- the rest should work as is):
subtype.value('SubtypeCode', 'nvarchar(max)') AS "SubtypeCode",
subtype.value('SubtypeName', 'nvarchar(max)') AS "SubtypeName",
subtype.value('descendant::DomainName', 'nvarchar(max)') AS "DomainName"
dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes ON
items.Type = itemtypes.UUID
items.Definition.nodes('//Subtypes/Subtype') AS Subtypes(subtype)) st INNER JOIN
codedValue.value('Code', 'nvarchar(max)') AS "DomainCode",
codedValue.value('Name', 'nvarchar(max)') AS "DomainValue"
dbo.GDB_ITEMS AS items INNER JOIN dbo.GDB_ITEMTYPES AS itemtypes
ON items.Type = itemtypes.UUID
items.Definition.nodes ('//CodedValues/CodedValue') AS CodedValues(codedValue)) dom ON st.DomainName = dom.Name