Monday, November 2, 2009

Primary Key , Foreign Key Column Name for Key Constraint


Query written below will give you result as shown in image.
SELECT
[constraint_name] = f.[name],
[child_table] = OBJECT_NAME(f.parent_object_id),
[child_column] = cc.name,
[parent_table] = OBJECT_NAME(f.referenced_object_id),
[parent_column] = pc.name
FROM
sys.foreign_keys f
INNER JOIN
(
SELECT
c.[object_id],
c.name,
c.column_id,
ic.index_id
FROM
sys.columns c
INNER JOIN
sys.index_columns ic
ON
c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
) AS pc
ON
f.key_index_id = pc.index_id
INNER JOIN
sys.foreign_key_columns fkc
ON
f.[object_id] = fkc.constraint_object_id
AND pc.[object_id] = fkc.referenced_object_id
AND fkc.referenced_column_id = pc.column_id
INNER JOIN
sys.columns cc
ON
fkc.parent_object_id = cc.[object_id]
AND fkc.parent_column_id = cc.column_id
ORDER BY
constraint_name,
child_table

Share This!


No comments:

Powered By Blogger · Designed By Seo Blogger Templates