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

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`