Query to List Tables and their Primary Keys

This query produces a resultset of two columns: every table in the current database, and the corresponding primary key expression if one exists:

; WITH Base AS (
SELECT
TABLE_NAME = QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME)
,C.Column_Name
,C.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON PK.TABLE_NAME = T.TABLE_NAME
AND PK.TABLE_SCHEMA = T.TABLE_SCHEMA
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
ON C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND C.TABLE_NAME = PK.TABLE_NAME
AND C.TABLE_SCHEMA = C.TABLE_SCHEMA
), OrderIt AS (
SELECT
TABLE_NAME
,Column_Name = CONVERT(varchar(max), Column_Name)
,ORDINAL_POSITION
,RecurseVar = ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY ORDINAL_POSITION)
FROM Base
), Recursed AS (
SELECT
Table_Name
,Column_Name
,RecurseVar
FROM OrderIt
WHERE RecurseVar = 1
UNION ALL
SELECT
B.Table_Name
,Column_Name = RTRIM(R.Column_Name) + ', ' + B.Column_Name
,B.RecurseVar
FROM Recursed R
JOIN OrderIt B
ON B.Table_Name = R.Table_Name
AND B.RecurseVar = R.RecurseVar + 1
), GetMax AS (
SELECT
Table_Name
,MAX_RecurseVar = MAX(RecurseVar)
FROM Recursed
GROUP BY Table_Name
), Results AS (
SELECT
R.Table_Name
,Primary_Key = R.Column_Name
FROM Recursed R
JOIN GetMax G
ON G.Table_Name = R.Table_Name
AND G.MAX_RecurseVar = R.RecurseVar
)
SELECT * FROM Results
ORDER BY Table_Name