Interesting. I wouldn't think even an EXISTS() column by column would be overall faster, but slower.
Because, if at least one column has no data, you will have to scan thru every occurence of that column, and thus scan the whole table, at least once anyway, since the EXISTS() will never get a hit.
Given that, I thought it was better to just scan the table only one time total, getting all the results needed in one pass.
If, say, three columns have no data, doing it column by column wouldn't I have to scan the table three times to determine that?
I used SUM() rather than MAX() to give an overall view of the table. For example, if the table has 15M rows, and the count returned is 1, you might review that one value and decide you could remove the column anyway. Whereas if the SUM() returned is, say, 275K, you would realize that the column is being used almost 2% of the time.
Scott Pletcher, SQL Server MVP 2008-2010