Запрос T-SQL для получения всех отношений базы данных и типа множественности отношений

Решение заключается в использовании "INFORMATION_SCHEMA" с запросом на получение всех ограничений, и в результате REFERENCED_TABLE_NAME является одной стороной, а FK_TABLE_NAME - многосторонней связью.

Запрос:

`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`

На самом деле невозможно показать отношение «многие ко многим», показав каждый внешний ключ в каждой таблице отдельно, поскольку для «многие ко многим» требуются два отдельных отношения из одной таблицы.

Вы можете проверить, является ли отношение «один к одному» или «многие к одному», проверив, есть ли уникальный индекс во внешней таблице, который соответствует точным столбцам внешнего ключа (ни больше, ни меньше).

Самый простой способ сделать это, вероятно, просто объединить внешние ключи в массив JSON, а затем разбить его обратно, используя 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;

дб<>рабочий пример

Логика OUTER APPLY в основном представляет собой реляционное деление:

• взять все уникальные индексы сторонней таблицы
• левое присоединение к нему массива JSON
• затем группируйтесь по index_id
• проверьте, что количество столбцов индекса равно обоим:
  • такой же, как длина массива (без пропущенных столбцов)
  • и столько же, сколько совпадений между столбцами (без дополнительных столбцов)