WITH ForeignKeys AS (
    SELECT
        fk.name AS ForeignKeyName,
        fkc.parent_object_id AS ChildTableID,
        fkc.referenced_object_id AS ParentTableID,
        fkc.parent_column_id AS ChildColumnID,
        fkc.referenced_column_id AS ParentColumnID
    FROM sys.foreign_keys fk
    JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
),
PrimaryKeys AS (
    SELECT
        i.object_id AS TableID,
        ic.column_id AS ColumnID,
        1 AS IsPrimaryKey
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.is_primary_key = 1
),
JunctionTables AS (
    SELECT
        fk1.parent_object_id AS JunctionTableID,
        fk1.referenced_object_id AS Table1ID,
        fk2.referenced_object_id AS Table2ID
    FROM sys.foreign_keys fk1
    JOIN sys.foreign_keys fk2
        ON fk1.parent_object_id = fk2.parent_object_id
        AND fk1.referenced_object_id <> fk2.referenced_object_id
)

SELECT
    DISTINCT c.object_id,
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.column_id AS ColumnID,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.is_nullable AS IsNullable,
    fk.ForeignKeyName AS RelationshipName,
    COALESCE(pk.IsPrimaryKey, 0) AS IsPrimaryKey,
    CASE WHEN fk.ChildTableID IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
    CASE
        WHEN pk.IsPrimaryKey = 1 AND fk.ChildTableID IS NOT NULL THEN '1:1'
        WHEN fk.ChildTableID IS NOT NULL THEN '1:N'
        WHEN jt.JunctionTableID IS NOT NULL THEN 'N:M'
        ELSE 'None'
    END AS RelationshipType
FROM sys.COLUMNS c
JOIN sys.TABLES t
    ON c.object_id = t.object_id
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
JOIN sys.types ty
    ON c.user_type_id = ty.user_type_id
LEFT JOIN ForeignKeys fk
    ON t.object_id = fk.ChildTableID AND c.column_id = fk.ChildColumnID
LEFT JOIN PrimaryKeys pk
    ON c.object_id = pk.TableID AND c.column_id = pk.ColumnID
LEFT JOIN JunctionTables jt
    ON t.object_id = jt.JunctionTableID
ORDER BY s.name, t.name, c.column_id;