Say I have a table with columns, colA, colB, colC and colD and there are 20 rows in my table. I want to write a sort of dynamic script to show me a count of non-null columns.

The result set would look something like

colA 5colB 20colC 10colD 1

This example would show that each row had colb populated in each of the 20 rows.

I know I could write a select that would generate some individual sql statements using syscolumns.name and syscolumns.id and I could then execute each of those statements but is there a way to do this more neatly ?