SQL Server : Tables Relationship Diagram Using TSQL Script

How do you create relationship diagram/report between tables of a given database ? Mostly people use Database Diagram for this purpose, but this can be achieved by TSQL script as following.

Download Script

 

-- Tables Relationship Script

-- Script By: Syed Muhammad Yasir for http://connectsql.blogspot.com
-- Updated August 1, 2012
 

SELECT  CASE WHEN a.parent_object_id IS NULL

THEN parent.name + '-1--*-' + child.name
ELSE parent.name + '-1--1-' + child.name
END AS TablesWithRelations
FROM    ( SELECT DISTINCT
parent_object_id, referenced_object_id
FROM      sys.foreign_keys ) fk
LEFT JOIN ( SELECT DISTINCT
fkindexes.parent_object_id,
fkindexes.referenced_object_id
FROM    ( SELECT    fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id, COUNT(*) cindexes
FROM      ( SELECT    object_id,
            parent_object_id,
            referenced_object_id
  FROM      ( SELECT    row_number() OVER ( PARTITION BY parent_object_id, referenced_object_id
ORDER BY object_id ) rid,
              object_id, parent_object_id, referenced_object_id
              FROM      sys.foreign_keys ) fk
  WHERE     rid = 1 ) fk
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fkc.parent_object_id
                                    AND ixcolumns.column_id = fkc.parent_column_id
JOIN sys.indexes ix ON ix.object_id = ixcolumns.object_id
                       AND ix.index_id = ixcolumns.index_id
WHERE     ix.is_unique = 1
GROUP BY  fk.parent_object_id,
fk.referenced_object_id,
ixcolumns.index_id ) fkindexes
JOIN ( SELECT   fk.parent_object_id,
    ixcolumns.index_id,
    COUNT(*) cindexestotal
FROM     ( SELECT DISTINCT
                parent_object_id
      FROM      sys.foreign_keys ) fk
    JOIN sys.index_columns ixcolumns ON ixcolumns.object_id = fk.parent_object_id
GROUP BY fk.parent_object_id,
    ixcolumns.index_id ) totalindexes ON totalindexes.parent_object_id = fkindexes.parent_object_id
                                         AND totalindexes.index_id = fkindexes.index_id

WHERE   cindexestotal - cindexes = 0 ) a ON a.parent_object_id = fk.parent_object_id

                        AND a.referenced_object_id = fk.referenced_object_id
JOIN sys.tables child ON fk.parent_object_id = child.object_id
JOIN sys.tables parent ON fk.referenced_object_id = parent.object_id
ORDER BY TablesWithRelations