T-SQL query to fetch all database relations and relation multiplicity type

Question

I need a T-SQL query to fetch all relations in a SQL Server database and understand that each relation is of multiple order like one-to-one, one-to-many and many-to-many relation.

I can fetch all relation's but can't know each relation is one-to-one, one-to-many.

My query is this :

SELECT
   FK.[name] AS ForeignKeyConstraintName
  ,SCHEMA_NAME(FT.schema_id) + '.' + FT.[name] AS ForeignTable
  ,STUFF(ForeignColumns.ForeignColumns, 1, 2, '') AS ForeignColumns
  ,SCHEMA_NAME(RT.schema_id) + '.' + RT.[name] AS ReferencedTable
  ,STUFF(ReferencedColumns.ReferencedColumns, 1, 2, '') AS ReferencedColumns,
  'one-to-one or one-to-many or many-to-many' as RelationType
FROM
    sys.foreign_keys FK
INNER JOIN 
    sys.tables FT ON FT.object_id = FK.parent_object_id
INNER JOIN 
    sys.tables RT ON RT.object_id = FK.referenced_object_id
CROSS APPLY
    (SELECT
         ', ' + iFC.[name] AS [text()]
     FROM
         sys.foreign_key_columns iFKC
     INNER JOIN 
         sys.columns iFC ON iFC.object_id = iFKC.parent_object_id
                         AND iFC.column_id = iFKC.parent_column_id
     WHERE
         iFKC.constraint_object_id = FK.object_id
     ORDER BY
         iFC.[name]
     FOR XML PATH('')) ForeignColumns (ForeignColumns)
CROSS APPLY
    (SELECT
         ', ' + iRC.[name] AS [text()]
     FROM
         sys.foreign_key_columns iFKC
     INNER JOIN 
         sys.columns iRC ON iRC.object_id = iFKC.referenced_object_id
                         AND iRC.column_id = iFKC.referenced_column_id
     WHERE
         iFKC.constraint_object_id = FK.object_id
     ORDER BY
         iRC.[name]
     FOR XML PATH('')) ReferencedColumns (ReferencedColumns)

What I can do ?

Answer 1

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)

Answer 2

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`