A friend of mine, John Paul Cook, has tricked out SQL Server 2005 database diagrams to show more than just relationships between tables.
Read the DevX article and download the code.
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.