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.name as RelationshipName,
    CASE
        WHEN ic.column_id = c.column_id AND ic.object_id = c.object_id THEN 1
        ELSE 0
    END AS IsPrimaryKey,
    CASE
        WHEN fk.parent_object_id = c.object_id THEN 1
        ELSE 0
    END AS IsForeignKey

FROM sys.columns c  -- Columns table
JOIN sys.tables t   -- Tables table join on columns.objectId and tables.objectid seems like they have the same ids
    ON c.object_id = t.object_id
JOIN sys.schemas s  -- inner join with the schemas on table schema_Id and the schemaId
    ON t.schema_id = s.schema_id
JOIN sys.types ty
    ON c.user_type_id = ty.user_type_id -- Inner join on tye types ID to get the columns types
LEFT JOIN sys.foreign_keys as fk
    ON t.object_id = fk.parent_object_id -- inner join to the foreign_keys to get if a column is a foreign key
LEFT JOIN sys.key_constraints pks
    ON pks.parent_object_id = c.object_id AND t.object_id = pks.parent_object_id --1
JOIN sys.index_columns ic ON pks.unique_index_id = ic.index_id AND pks.parent_object_id = ic.object_id -- 3 to get enough data to determine the PK

WHERE s.name = 'dbo'

ORDER BY s.name, t.name, c.column_id;