For SQL Server 2005 +
DECLARE @tableName nvarchar(MAX) SET @tableName = 'tableName' -- (1) SELECT tc.TABLE_NAME, tc.CONSTRAINT_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.TABLE_NAME = @tableName OR @tableName IS NULL ORDER BY tc.TABLE_NAME, kcu.ORDINAL_POSITION
Notes:
- Change line (1) to specify the table name. Removing this line will list PRIMARY KEYS for all TABLES.