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