I was working on database design stuff and came across a situation where in i had to get all the primary keys existing in database for all tables and the primary key columns and column details. It would be very had and insane to go into each table and search for PK and its data. So i came up with this small script which gives you all information on Primary Keys. This can be modified as per your needs to get more information or for foreign keys ets. Hope this is useful for some guys looking for it. It was very helpful for me though :)

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;

Happy Coding.

Thanks and Regards,

Manjunath C Bhat