I wasn't able to find a solution so I created a custom view to tie up both and I query this using dapper. If anyone can see anyway to make this work more effectively please let me know.

View code:

SELECT        dbo.cmsPropertyType.dataTypeId, dbo.cmsPropertyType.Name, dbo.cmsPropertyType.Alias, dbo.cmsDataType.propertyEditorAlias
FROM            dbo.cmsPropertyType LEFT OUTER JOIN
                         dbo.cmsDataType ON dbo.cmsPropertyType.dataTypeId = dbo.cmsDataType.nodeId

Dapper Code (Requires Dapper nuget)

/// <summary>
    /// Service to retrieve database custom view values
    /// </summary>
    public class CustomViewService
    {
        /// <summary>
        /// Return a custom view with the datatype relationships in them as no
        /// service provided by umbraco matches these up.
        /// </summary>
        /// <returns></returns>
        public List<DataTypePropertyRelationship> GetDataTypeProperties()
        {
            try
            {
                IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString);
                //Manually need to open and close the connection
                db.Open();
                string SqlString = "SELECT * FROM [vwDataTypePropertyRelationship]";
                var dataProperties = (List<DataTypePropertyRelationship>)db.Query<DataTypePropertyRelationship>(SqlString);
                db.Close();
                return dataProperties;
            }
            catch(Exception e)
            {
                //Logging required
                return new List<DataTypePropertyRelationship>();
            }
        }
    }

Dto

public class DataTypePropertyRelationship
{
    public int dataTypeId { get; set; }
    public string Name { get; set; }
    public string Alias { get; set; }
    public string PropertyEditorAlias { get; set; }
}

Cheers,

M

Hi Marc

This package will show you how your datatypes are being used in doctypes :

https://our.umbraco.com/packages/developer-tools/diplo-god-mode/

And many more stuff. Maybe have a peek in the source code of that package to see how it's being handled there.

Dave

is working on a reply...