To identify Clustered and Non-Clustered Primary key table(s) in the Database ?

To fetch all the Primary key tables in the current Database.

Use SQLServerBuddy
Go

SELECT
O.[NAME] 'Table Name',
I.[NAME] 'Key Name',
CASE I.[TYPE] WHEN 2 THEN 'Non Clustered Primary Key' ELSE 'Clustered Primary Key' END 'Clustered /NonClustered'
FROM SYS.INDEXES I JOIN SYS.OBJECTS O
ON (I.[OBJECT_ID] = O.[OBJECT_ID])
WHERE O.[TYPE] = 'U'
AND I.IS_PRIMARY_KEY = 1
ORDER BY 1
 
The column Type in Sys.Indexes DMV have the following values
 
0 - HEAP
1 - CLUSTERED
2 - NONCLUSTERED