Edit: At the suggestion of a much knowledgable commenter who shall remain named Aaron, I changed from using schema_name() function to using sys.tables. When writing code that is expected to have reuse, it can be safer to use the tables rather than functions because the tables will work in the context of the database that is in the from ...

Very often, I find myself wanting to query system metadata for columns. Some of the metadata is fairly easy to deal with, like if you want to find column names, just simply querying sys.columns is very straightforward.  But if you want to query data types, it gets stickier.  There are type types listed, one that is the declared type, and ...