Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Created July 31, 2012 16:27
Show Gist options
  • Save lionofdezert/3218251 to your computer and use it in GitHub Desktop.
Save lionofdezert/3218251 to your computer and use it in GitHub Desktop.
SQL Server : Tables Relationship Report Through TSQL
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment