Let me preface this by saying, this is not a pure SQL solution. However, if you're having difficulty isolating correct XML tags and xml locations for all the tables, feature classes, and potentially other datasets to get all default values, you could also look into using arcpy (ArcMap, ArcServer, or ArcPro python modules) and walk a database (https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/walk.htm) and for each relevant dataset, use List Fields (https://pro.arcgis.com/en/pro-app/latest/arcpy/functions/listfields.htm) to get field objects (https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/field.htm) with various details, including all of the requested info. You could then write out that data to a CSV or to a table of your choice that could then be accessed via SQL. Again, not a perfect solution for your request, but it could give you all defaults for all field types in all datasets at one time and in one output if you so desired.

Also, as an FYI, this (and likely your solution as well) assumes Subtypes are not being used. If you have subtypes enabled in one of your datasets, each subtype can store its own field defaults, which I'm almost positive are stored elsewhere. I'm not sure about them in the back-end, but they can also be accessed via python. Use List Subtypes (https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/listsubtypes.htm). I will warn you though, the documentation is very lacking for this. My suggestion, initially, would be to just use arcpy.da.ListSubtypes(FullPathToDataset) on a dataset that has subtypes, print the entire resulting dictionary, and get a sense for it before you try writing the actual code to extract the desired info. It's pretty straight-forward, but it can take a little sorting through to understand the subtype dictionary.

Let me preface this by saying, this is not a pure SQL solution. However, if you're having difficulty isolating correct XML tags and xml locations for all the tables, feature classes, and potentially other datasets to get all default values, you could also look into using arcpy (ArcMap, ArcServer, or ArcPro python modules) and walk a database (https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/walk.htm) and for each relevant dataset, use List Fields (https://pro.arcgis.com/en/pro-app/latest/arcpy/functions/listfields.htm) to get field objects (https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/field.htm) with various details, including all of the requested info. You could then write out that data to a CSV or to a table of your choice that could then be accessed via SQL. Again, not a perfect solution for your request, but it could give you all defaults for all field types in all datasets at one time and in one output if you so desired.

Also, as an FYI, this (and likely your solution as well) assumes Subtypes are not being used. If you have subtypes enabled in one of your datasets, each subtype can store its own field defaults, which I'm almost positive are stored separately from the standard field defaults. I'm not sure about their location in the back-end system tables, but they can also be accessed via python. Use List Subtypes (https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/listsubtypes.htm). I will warn you though, the documentation is very lacking for this. My suggestion, initially, would be to just use arcpy.da.ListSubtypes(FullPathToDataset) on a dataset that has subtypes, print the entire resulting dictionary, and get a sense for it before you try writing the actual code to extract the desired info. It's pretty straight-forward, but it can take a little sorting through to understand the subtype dictionary.