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