John Morehouse shares some good information on composite keys, including a few scripts:
As I started to work on this, my first thought was that it would be helpful to know how many tables had a composite primary key. This would give me an idea on how many tables I was dealing with. Thankfully, SQL Server has this information by using system DMVs (dynamic management views) along with the COL_NAME function.
Note: the COL_NAME function will only work with SQL Server 2008 and newer.
All of this time, I’d never known about COL_NAME.