Declarative Referential Integrity - Get Primary Key Columns

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        4 Jul 2012

Description

This function returns all primary 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_GetPrimaryKeyColumns
GO

CREATE FUNCTION dbo.udf_GetPrimaryKeyColumns(@TableName  SYSNAME = NULL)
RETURNS @udf_PrimaryKeyColumns TABLE
  
(
      
DatabaseName    SYSNAME NULL,
      
PK_TableSchema  SYSNAME NULL,
      
PK_TableName    SYSNAME NULL,
      
PK_Schema       SYSNAME NULL,
      
PK_Name         SYSNAME NULL,
      
PK_ColumnName   SYSNAME NULL,
      
PK_ColumnOrder  INT
  
)
AS
/***************************************************************
Purpose:   Lists the tables and columns involved in PK constraints

           The following DMVs are used:
               sys.key_constraints         - contains details of all non FK constraints.
               sys.columns                 - list all column details in the db
               sys.objects                 - list all object details in the db
                                              
          
Author:        ChillyDBA
History:   4 Jul 2012

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

BEGIN
   INSERT
@udf_PrimaryKeyColumns
      
(
          
DatabaseName,
          
PK_TableSchema,
          
PK_TableName,
          
PK_Schema,
          
PK_Name,
          
PK_ColumnName,
          
PK_ColumnOrder
      
)
  
SELECT
      
DB_NAME()                           AS DatabaseName,
      
SCHEMA_NAME(t.schema_id)            AS PK_TableSchema,
      
t.name                              AS PK_TableName,
      
SCHEMA_NAME(k.schema_id)            AS PK_Schema,
      
k.name                              AS PK_Name,
      
COL_NAME(c.OBJECT_ID, c.column_id)   AS PK_ColumnName,
      
c.key_ordinal                       AS PK_ColumnOrder
  
FROM sys.key_constraints k
  
INNER JOIN sys.objects so
      
ON k.parent_object_id = so.OBJECT_ID
  
INNER JOIN sys.index_columns c
          
ON c.OBJECT_ID = k.parent_object_id
          
AND c.index_id = k.unique_index_id
  
INNER JOIN sys.tables t
      
ON t.OBJECT_ID = k.parent_object_id
  
WHERE k.TYPE = 'PK'
  
ORDER BY t.name, c.key_ordinal

  
  
RETURN
END



Test Code:

 

USE AdventureWorksDW
GO

SELECT * FROM dbo.udf_GetPrimaryKeyColumns(NULL)

SELECT * FROM dbo.udf_GetPrimaryKeyColumns('DimProduct')


ċ
udf_GetPrimaryKeyColumns.sql
(2k)
Andy Hughes,
Jul 4, 2012, 12:24 PM
Comments