I've been asked how to do this about a dozen times by developers who were not very entrenched with SQL and I've had to do it my self a handful of times.

I know the column name (or at least part of it) but I have no idea what table in this massive DB it lives in; how do I find it?

A simple query will net you the results you need:

SELECT
    so.name AS [Table], sc.name AS [Column],
    so.xType AS [Table Type], st.name AS [Data Type]
FROM dbo.syscolumns AS sc
    LEFT JOIN dbo.sysobjects AS so
        ON so.id = sc.id
    LEFT JOIN dbo.systypes AS st
        ON st.xtype = sc.xType
WHERE sc.name LIKE '%Partial Column Name Here%'
ORDER BY so.xType, so.name, sc.name

My example is with a LIKE search because In my most recent use of this query (which made me think to post this) I didn't even really know the column name, just a specific 3 letter code that I was hoping would be part of the full name. Nothing ground breaking or exciting here, but I'm sure this will help someone someday searching google :)