It's not really possible to show Many-to-Many relationship by showing each foreign key on each table separately, because a Many-to-Many requires two separate relationships from a single table.

You can check if a relationship is One-to-One or Many-to-One, by checking if there is a unique index on the foreign table which matches the exact columns of the foreign key (no more, no less).

The easiest way to do this is probably to just aggregate the foreign keys into a JSON array then break it bac out using OPENJSON .

SELECT
  FK.name AS ForeignKeyConstraintName,
  FS.name AS ForeignTableSchema,
  FT.name AS ForeignTable,
  Columns.ForeignColumns,
  RS.name AS ReferencedTableSchema,
  RT.name AS ReferencedTable,
  Columns.ReferencedColumns,
  IIF(i.isUniqueColumnSet = 1, 'one-to-one', 'many-to-one') as RelationType
FROM sys.foreign_keys FK
JOIN sys.tables FT ON FT.object_id = FK.parent_object_id
JOIN sys.schemas FS ON FS.schema_id = FT.schema_id
JOIN sys.tables RT ON RT.object_id = FK.referenced_object_id
JOIN sys.schemas RS ON RS.schema_id = RT.schema_id
CROSS APPLY (
    SELECT
      ForeignColumns = STRING_AGG(FC.name, ', ')
          WITHIN GROUP (ORDER BY FKC.constraint_column_id),
      ForeignColumnIds = '[' + STRING_AGG(FKC.parent_column_id, ', ')
          WITHIN GROUP (ORDER BY FKC.constraint_column_id) + ']',
      ReferencedColumns = STRING_AGG(RC.name, ', ')
          WITHIN GROUP (ORDER BY FKC.constraint_column_id),
      ColumnCount = COUNT(*)
    FROM sys.foreign_key_columns FKC
    JOIN sys.columns FC
      ON FC.object_id = FKC.parent_object_id
     AND FC.column_id = FKC.parent_column_id
    JOIN sys.columns RC
      ON RC.object_id = FKC.referenced_object_id
     AND RC.column_id = FKC.referenced_column_id
    WHERE FKC.constraint_object_id = FK.object_id
) Columns
OUTER APPLY (
    SELECT TOP (1)
      isUniqueColumnSet = MAX(1)
    FROM sys.indexes i
    JOIN sys.index_columns ic
      ON ic.object_id = i.object_id
     AND ic.index_id = i.index_id
    LEFT JOIN OPENJSON(Columns.ForeignColumnIds) ids
      ON CAST(ids.value AS int) = ic.column_id
    WHERE i.object_id = FT.object_id
      AND i.is_unique = 1
    GROUP BY
      i.index_id
    HAVING COUNT(*) = Columns.ColumnCount
       AND COUNT(*) = COUNT(CAST(ids.value AS int))
) i;

db<>fiddle

The logic of the OUTER APPLY is basically a Relational Division:

• take all unique indexes of the foreign table

• left-join the JSON array to it

• then group up by index_id

• check that the number of index columns is both:

• the same as the length of the array (no missing columns)

• and the same as the number of matches between columns (no extra columns)

The solution is use "INFORMATION_SCHEMA" with a query i fetch all constraints and in result REFERENCED_TABLE_NAME is one side and FK_TABLE_NAME is many side of relation

Query Is :

`SELECT 
    KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
   
   
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
order by KCU2.TABLE_NAME`