The following query lists all the Primary keys of all the table.
OP has requested to list all PK and composite PKs and concatenate all it’s composite PK column values into a single row separated by comma hence this post.
SELECT TAB.TABLE_NAME,COL.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TAB,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE COL
WHERE
COL.CONSTRAINT_NAME = TAB.CONSTRAINT_NAME
AND COL.TABLE_NAME = TAB.TABLE_NAME
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
In the above screenshot the invoices and PK_TEST table has composite PK’s. Query to get the Composite PKs in a single row separated by comma
SELECT TABLENAME, LEFT(COL,LEN(COL)-1) AS COLUMN_NAME FROM ( SELECT DISTINCT TAB.TABLE_NAME TABLENAME, ( SELECT COL.COLUMN_NAME +',' AS [text()] FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE COL WHERE COL.CONSTRAINT_NAME = TAB.CONSTRAINT_NAME AND COL.TABLE_NAME = TAB.TABLE_NAME AND CONSTRAINT_TYPE = 'PRIMARY KEY' FOR XML PATH ('') ) COL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TAB )T where T.COL is NOT NULL
