Get Foreign Key Columns

DROP FUNCTION dbo.udf_GetForeignKeyColumns

GO

CREATE FUNCTION dbo.udf_GetForeignKeyColumns(@TableName SYSNAME = NULL)

RETURNS @udf_ForeignKeyColumns TABLE

(

DatabaseName SYSNAME NULL,

FK_TableName SYSNAME NULL,

FK_Name SYSNAME NULL,

FK_ColumnName SYSNAME NULL,

FK_ColumnOrder INT,

PK_TableName SYSNAME NULL,

PK_ColumnName SYSNAME NULL

)

AS

/***************************************************************

Purpose: Lists the tables and columns involvde in FK constraints

The following DMVs are used:

sys.foreign_key_columns - from SQL 2005 onwards, FK relationships are surfaced

through this view instead of needing to traverse the

complexity in sysconstraints

sys.columns - list all column details in the db

Author: Unknown

History: 5 Nov 2007

****************************************************************/

BEGIN

INSERT @udf_ForeignKeyColumns

(

DatabaseName,

FK_TableName,

FK_Name,

FK_ColumnName,

FK_ColumnOrder,

PK_TableName,

PK_ColumnName

)

SELECT

DB_NAME() AS DatabaseName,

OBJECT_NAME(parent_object_id) AS FK_TableName,

OBJECT_NAME(constraint_object_id) AS FK_Name,

c.name AS FK_ColumnName,

constraint_column_id AS FK_ColumnOrder,

OBJECT_NAME(referenced_object_id) AS PK_TableName,

r.name AS PK_ColumnName

FROM sys.foreign_key_columns f

LEFT JOIN sys.columns c

ON f.parent_object_id=c.OBJECT_ID

AND f.parent_column_id=c.column_id

LEFT JOIN sys.columns r

ON f.referenced_object_id=r.OBJECT_ID

AND f.referenced_column_id = r.column_id

-- c.column_id included in ordering so the FKs are ordered by the ordinal position of the column

-- (for easy comparison to ERDs or Management Studio dialog boxes/generated code)

WHERE parent_object_id = ISNULL(OBJECT_ID(@TableName), parent_object_id)

ORDER BY FK_TableName, c.column_id, FK_ColumnOrder

RETURN

END