List all the Foreign keys in a database

Below query will give a list of all the foreign keys in a database.

WITH CTE_ForeignKeyDetails 

AS

(

SELECT f.name AS ForeignKey,

SCHEMA_NAME(f.SCHEMA_ID) + '.'+ OBJECT_NAME(f.parent_object_id) AS 'Referencing_Table',

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Referencing_Column',

SCHEMA_NAME(o.SCHEMA_ID) + '.'+ OBJECT_NAME (f.referenced_object_id) AS 'Reference_Table',

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS 'Reference_Column'

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

)

SELECT

a.ForeignKey,

a.Referencing_Table, 

STUFF((SELECT ', ' + b.[Referencing_Column] FROM CTE_ForeignKeyDetails b  WHERE a.ForeignKey = b.ForeignKey ORDER BY b.[Referencing_Column] FOR XML PATH('')), 1, 1, '') Referencing_Column,

a.Reference_Table ,

STUFF((SELECT ', ' + c.Reference_Column FROM CTE_ForeignKeyDetails c  WHERE a.ForeignKey = c.ForeignKey ORDER BY c.Reference_Column FOR XML PATH('')), 1, 1, '') Reference_Column

FROM CTE_ForeignKeyDetails   a

GROUP BY a.ForeignKey, a.Referencing_Table,a.Reference_Table

ORDER BY a.Referencing_Table

Although we can use only part which is in cte, but if we have a combined foreign key, then this will give more than one rows. 

So in below part we using cte and combining all such rows in one column using FOR XML PATH.