Sean mine was just a lazy adaptation of something that already existed; it was easier than from- scratch script to find them; this adapted snippet from the middle of your post is probably boatloads easier to understand, and has pretty much the same reuslt si did (qualifying indexes only...not the actual query)

select t.name as TableName

, c.name as ColumnName,

i.name,

c.name

from sys.indexes i

inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id

inner join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id

inner join sys.tables t on t.object_id = i.object_id

where i.is_primary_key = 0 --primary key can't be null

and c.is_nullable = 1 --don't look at the column if it is not nullable