ms sql - find relational tables in database

Question

Is there a way to find the relationship between tables in a database via a query or GUI in SQL Express 2005?

Other then manually looking for matching primary/foreign keys in tables is there any easier way to do it?

Answer 1

In SSMS right click on a table and select design.

There is a button that looks like 2 or 3 windows with a line between them, this shows the relationships for that table.

This will open up the ForeignKey window which will show each relationship between that table and every other table it relates to, it will also show what fields connect those tables.

Answer 2

I just import the definitions to Visio. Maybe it helps ...

http://office.microsoft.com/en-us/visio-help/reverse-engineer-an-existing-database-into-a-database-model-HA010115485.aspx

Answer 3

I believe the script you are looking for comes from the following link:

http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/

Here is the script:

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
--ORDER BY 1,2,3,4
--WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
--WHERE PK.TABLE_NAME IN ('one_thing', 'another')
--WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Notice that you can add some optional statements at the bottom to limit or order your results. The output will list the table that contains the foreign key, the name of the field, the primary key table and field that it refers to and the name the constraint has been given.