Know relationships between all the tables of database in SQL Server

I wish to all know how the tables in my database are related to each other (i.e PK/FK/UK) and hence i created a database diagram of all my tables in SQL Server. The diagram that was created was not easily readable and had to scroll (horizontally and sometimes vertically) to see the table on the other end.

In short SQL’s db diagram are not UI friendly when it comes to knowing relationships between many tables.

My (simple) Question: Is there something like database diagram which can do what db diagram did but in “good” way?

Answer

Sometimes, a textual representation might also help; with this query on the system catalog views, you can get a list of all FK relationships and how the link two tables (and what columns they operate on).

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

Dump this into Excel, and you can slice and dice – based on the parent table, the referenced table or anything else.

I find visual guides helpful – but sometimes, textual documentation is just as good (or even better) – just my 2 cents…..