Get the primary keys for all user-defined tables in the SQL Server database.

get_pk.sql

-- File Name:	get_pk.sql
-- Description:	Get the primary keys.

USE AdventureWorks2019;

GO

SELECT 

		SCHEMA_NAME(tab.schema_id)	AS [Schema]
    ,	pk.name						AS [Primary Key Name]
    ,	tab.name					AS [Table Name]
    ,	col.name					AS [Column Name] 
    ,	ic.index_column_id			AS [Column ID]

from sys.tables tab

    INNER JOIN sys.indexes pk
        ON		tab.object_id		= pk.object_id 
			AND pk.is_primary_key	= 1

    INNER JOIN sys.index_columns ic
        ON		ic.object_id		= pk.object_id
			AND ic.index_id			= pk.index_id

    INNER JOIN sys.columns col
        ON		pk.object_id		= col.object_id
			and col.column_id		= ic.column_id

ORDER BY [Schema], [Primary Key Name], [Column ID];

GO