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 etc. 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