Get Foreign Key Columns

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 5 Nov 2007

Description

This function returns all foreign key names and columns for both the FK and PK tables for one or all tables in a database.

It may seem like a repetition of the functionality of the code in the stored procedure usp_ShowColumnUsage, but that really produces an output that is generally useful for gaining an overview of DRI in a database, whereas this function produces an output that can be directly utilized to drive processes, whether that be scripting or other dynamic SQL operations.

Code

Function:

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

Test Code:

USE AdventureWorksDW

GO

SELECT * FROM dbo.udf_GetForeignKeyColumns(NULL)

SELECT * FROM dbo.udf_GetForeignKeyColumns('FactInternetSales')