Great question, thanks.
Having worked on a database where tables had been defined with different collations to the default, and making use of SQL_VARIANT types, this was moderately simple.
I would echo Hugo's comment re the use of SQL_VARIANT: use only as a last resort.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”