Pages

Tuesday, October 4, 2011

TSQL to get All Primary Key Columns and its relevant details

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;