I had to write a SQL to get all the tables with their primary keys separated by commas.
So I thought I will share this
This query will select tables inside a specific schema, which is specified in the where clause ('anyschema')

WITH Ranked ( TABLE_ID, TABLE_NAME, rnk, COLUMN_NAME ) AS 
( 
    SELECT TABLE_ID, TABLE_NAME, 
           ROW_NUMBER() OVER( PARTITION BY TABLE_ID ORDER BY TABLE_ID ),
           CAST( COLUMN_NAME AS VARCHAR(8000) )
    FROM (
        SELECT  ST.object_id AS TABLE_ID, ST.name AS TABLE_NAME, SC.object_id AS COLUMN_ID, SC.name AS COLUMN_NAME
        FROM sys.key_constraints AS SKC INNER JOIN
             sys.tables AS ST ON ST.object_id = SKC.parent_object_id INNER JOIN
             sys.schemas AS SS ON SS.schema_id = ST.schema_id INNER JOIN
             sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id INNER JOIN
             sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id INNER JOIN
             sys.types AS STY ON SC.user_type_id = STY.user_type_id
        WHERE (SS.name = 'anyschema')
    ) a
),
AnchorRanked ( TABLE_ID, TABLE_NAME, rnk, COLUMN_NAME ) AS ( 
    SELECT TABLE_ID, TABLE_NAME, rnk, COLUMN_NAME
    FROM Ranked
    WHERE rnk = 1 
),
RecurRanked ( TABLE_ID, TABLE_NAME, rnk, COLUMN_NAME ) AS ( 
    SELECT TABLE_ID, TABLE_NAME, rnk, COLUMN_NAME
    FROM AnchorRanked
    UNION ALL
    SELECT Ranked.TABLE_ID, Ranked.TABLE_NAME, Ranked.rnk,
            RecurRanked.COLUMN_NAME + ', ' + Ranked.COLUMN_NAME
    FROM Ranked INNER JOIN 
            RecurRanked ON Ranked.TABLE_ID = RecurRanked.TABLE_ID 
                        AND Ranked.rnk = RecurRanked.rnk + 1 
)
SELECT TABLE_NAME, MAX( COLUMN_NAME )
FROM RecurRanked
GROUP BY TABLE_NAME;