T-SQL to Find All Primary keys in a SQL Server Database

You want to know how many Primary Keys are existing on a SQL Server database? Below script can assist you in all the details of all Primary Keys in a Database. Simply run the below script against the database for which you want to get the Primary Key details.

SELECT SS.NAME AS [TABLE_SCHEMA], ST.NAME AS [TABLE_NAME]
     , SKC.NAME AS [CONSTRAINT_NAME], SC.NAME AS [CONSTRAINT_COLUMN_NAME],
     CAST(STY.NAME AS VARCHAR(20)) +'('+
     CAST(CASE ST.NAME
     WHEN 'NVARCHAR' THEN (SELECT SC.MAX_LENGTH/2)
     ELSE (SELECT SC.MAX_LENGTH)
     END AS VARCHAR(20)) +')' AS [DATA_TYPE]
  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
  ORDER BY TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME;