Declarative Referential Integrity - 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')


ċ
udf_GetForeignKeyColumns.sql
(3k)
Andy Hughes,
Jul 4, 2012, 11:17 AM
Comments