In foreign key relationship show parent and child columns as comma separated values

Transact-SQL
1

First I tried the net but couldn't find any script where
columns from parent table and child table in a foreign
key relationship is shown as a comma separated values.

For example

SELECT 
    obj.name      AS FK_NAME,
    tab1.name     AS [table],
    col1.name     AS [parent_column],
    tab2.name     AS [referenced_table],
    col2.name     AS [referenced_column]
FROM 
     sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id 
        AND col2.object_id =  tab2.object_id
        
where tab1.name = 'mth_att_mst'
or tab1.name ='emp_pic'

Result is

FK_NAME table parent_column referenced_table referenced_column
FK_emp_pic_to_employee_emp_code_n_company_code emp_pic emp_code employee emp_code
FK_emp_pic_to_employee_emp_code_n_company_code emp_pic company_code employee company_code
FK_mth_att_mst_employee mth_att_mst emp_code employee card_code

The parent_column and referenced_column is showing in different rows.
For row 1 and row 2 there should have been one row.

The expected result

FK_NAME table parent_column referenced_table referenced_column
FK_emp_pic_to_employee_emp_code_n_company_code emp_pic emp_code,company_code employee emp_code,company_code
FK_mth_att_mst_employee mth_att_mst emp_code employee card_code

a better view of the expected result

Capture
Capture802×73 17.1 KB

2

You can use CTE and Stuff

;with cte as(SELECT 
    obj.name      AS FK_NAME,
    tab1.name     AS [table],
    col1.name     AS [parent_column],
    tab2.name     AS [referenced_table],
    col2.name     AS [referenced_column]
FROM 
     sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id 
        AND col2.object_id =  tab2.object_id
)

SELECT 
    c.FK_NAME,
    c.[table],
	
	STUFF( 
	        ( select ',' + c1.parent_column
			   from cte c1
			  where c1.fk_Name = c.fk_name
				and c1.[table] = c.[table]
				and c1.[referenced_table] = c.[referenced_table]
	           FOR XML PATH('') 
	        ), 
	        1, 
	        1,'' 
	      ) AS [parent_column],
    c.[referenced_table],

STUFF( 
	        ( select ',' + c1.[referenced_column]
			   from cte c1
			  where c1.fk_Name = c.fk_name
				and c1.[table] = c.[table]
				and c1.[referenced_table] = c.[referenced_table]
	           FOR XML PATH('') 
	        ), 
	        1, 
	        1,'' 
	      ) AS [referenced_column]
FROM cte c

group by c.FK_NAME,
    c.[table],
	c.[referenced_table]
order by 1, 2, 3
3

@mike01

Solved!

Thanks and regards