Yep... I know this is an old post but I've just gone through an escapade with SQL_Variant with great success.
I agree that the "one table" idea is typically a very bad one. It does make applications quite flexible because users can easily create their own attributes for an entity but, as well all know too well, there are large problems down that particular rabbit hole.
I will say, however, that it makes a hell of a nice "V" column for an EAV style of Audit table especially since it stores some meta-data about the datatype which can be easily returned. Yes, you can certainly get the datatype by interrogating sys.columns or INFORMATION_SCHEMA.Columns but then the audit table wouldn't be recording the datatype changes for free. We also use the Audit table for certain types of reporting where the output needs to be formatted according to datatype. In the previous audit table (the one I'm replacing), they stored formatted data instead of the raw data because they didn't want to go through the hassle of looking up the data type according to the "EA" (which could change, as previously stated).
There are some caveats with storing Blob, TimeStamp, and SQL_Variant (from another table) datatypes but the SQL_Variant datatype in an audit table was just what the doctor ordered for the types of things I need to do. It's pretty darned fast, too.