Object Annotations data mapping

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.edition annotationNote,
 'TMS edition field' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 Objects o
WHERE
 o.edition IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.curatorialRemarks annotationNote,
 'TMS curatorial remarks' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 Objects o
WHERE
 o.curatorialRemarks IS NOT NULL
UNION ALL
SELECT
 CAST(c.objectID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component prep comments' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) = 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 'c' + CAST(c.componentID AS VARCHAR) objectID,
 c.prepComments annotationNote,
 'Component prep comments' annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 ObjComponents c
WHERE
 c.objectID IN (SELECT t.objectID FROM ObjComponents t GROUP BY t.objectID HAVING count(*) > 1)
 AND c.prepComments IS NOT NULL
UNION ALL
SELECT
 CAST(u.objectID AS VARCHAR) objectID,
 u.fieldValue annotationNote,
 substring(t.userFieldType, 1, charindex(' ', t.userFieldType) - 1) + lower(substring(t.userFieldType, charindex(' ', t.userFieldType), len(t.userFieldType))) annotationType,
 NULL annotationDate,
 NULL annotationAuthor
FROM
 ObjUserFields u
 JOIN UserFieldTypes t ON u.fieldTypeID = t.userFieldTypeID
WHERE
 u.FieldTypeID != 17
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 t.TextEntry annotationNote,
 substring(REPLACE(tt.TextType, 'Coin: ', ''), 1, charindex(' ', REPLACE(tt.TextType + ' ', 'Coin: ', '')) - 1) + lower(substring(REPLACE(tt.TextType, 'Coin: ', ''), charindex(' ', REPLACE(tt.TextType + ' ', 'Coin: ' , '')), len(tt.TextType))) annotationType,
 t.TextDate annotationDate,
 t.AuthorConID annotationAuthor
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (2, 11, 13, 16, 17, 18, 21, 24, 25, 26, 28, 29, 30, 36, 44, 51, 55, 64, 66, 67, 68, 71, 72, 74, 80, 83, 88, 94, 95, 98, 105, 107, 109, 118, 120, 122)
;