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')