1. WITH ForeignKeys AS (
  2.     SELECT  
  3.         fk.name AS ForeignKeyName,
  4.         fkc.parent_object_id AS ChildTableID,
  5.         fkc.referenced_object_id AS ParentTableID,
  6.         fkc.parent_column_id AS ChildColumnID,
  7.         fkc.referenced_column_id AS ParentColumnID
  8.     FROM sys.foreign_keys fk
  9.     JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
  10. ),
  11. PrimaryKeys AS (
  12.     SELECT  
  13.         i.object_id AS TableID,
  14.         ic.column_id AS ColumnID,
  15.         1 AS IsPrimaryKey
  16.     FROM sys.indexes i
  17.     JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
  18.     WHERE i.is_primary_key = 1
  19. ),
  20. JunctionTables AS (
  21.     SELECT
  22.         fk1.parent_object_id AS JunctionTableID,
  23.         fk1.referenced_object_id AS Table1ID,
  24.         fk2.referenced_object_id AS Table2ID
  25.     FROM sys.foreign_keys fk1
  26.     JOIN sys.foreign_keys fk2
  27.         ON fk1.parent_object_id = fk2.parent_object_id
  28.         AND fk1.referenced_object_id <> fk2.referenced_object_id
  29. )
  30.  
  31. SELECT  
  32.     DISTINCT c.object_id,
  33.     s.name AS SchemaName,
  34.     t.name AS TableName,
  35.     c.name AS ColumnName,
  36.     c.column_id AS ColumnID,
  37.     ty.name AS DataType,
  38.     c.max_length AS MaxLength,
  39.     c.is_nullable AS IsNullable,
  40.     fk.ForeignKeyName AS RelationshipName,
  41.     COALESCE(pk.IsPrimaryKey, 0) AS IsPrimaryKey,
  42.     CASE WHEN fk.ChildTableID IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
  43.     CASE
  44.         WHEN pk.IsPrimaryKey = 1 AND fk.ChildTableID IS NOT NULL THEN '1:1'
  45.         WHEN fk.ChildTableID IS NOT NULL THEN '1:N'
  46.         WHEN jt.JunctionTableID IS NOT NULL THEN 'N:M'
  47.         ELSE 'None'
  48.     END AS RelationshipType
  49. FROM sys.COLUMNS c  
  50. JOIN sys.TABLES t  
  51.     ON c.object_id = t.object_id
  52. JOIN sys.schemas s  
  53.     ON t.schema_id = s.schema_id
  54. JOIN sys.types ty  
  55.     ON c.user_type_id = ty.user_type_id
  56. LEFT JOIN ForeignKeys fk  
  57.     ON t.object_id = fk.ChildTableID AND c.column_id = fk.ChildColumnID
  58. LEFT JOIN PrimaryKeys pk  
  59.     ON c.object_id = pk.TableID AND c.column_id = pk.ColumnID
  60. LEFT JOIN JunctionTables jt  
  61.     ON t.object_id = jt.JunctionTableID
  62. ORDER BY s.name, t.name, c.column_id;