I'm brand new to SQL Server 2008, and have some newbie questions about the diagram pane. I just dragged two tables onto it to do an inner join, and the console "knew" to create a one-to-many relationship between them. Where is this information kept in the Management Studio for me to look at closer?
Thanks!
One of the tables has a foreign key referencing the other table. In Object Explorer, expand the information for the tables involved and look under Keys.
Much of this information is actually available in system tables in the database containing the user tables.
Here are two of the available tables of interest. sys.tables contains table information, sys.foreign_keys contains relationship information via foreign key constraints.
sys.tables (Transact-SQL) @ MSDN
sys.foreign_keys (Transact-SQL) @MSDN
Also handy is the following information on Pinal Dave's site on using this information for your own lookups.
Find tables with Foreign Key Constraint in Database @ SQLAuthority.com
Find Relationship of Primary key and Foreign Key using T-SQL @ SQLAuthority.com