sql : Know relationships between all the tables of database in SQL Server

SELECT
    fk.name      as 'Foreign Key Name',
    tp.name      as 'Foreign Key Table Name',
    cp.column_id as 'Foreign Key Column ID',      
    cp.name      as 'Foreign Key Column Name' ,  
    tr.name      as 'Primary Key Table Name',
    cr.column_id as 'Primary Key Column ID',
    cr.name      as 'Primary Key Column Name'
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