TSQL to determine primary key columns of a given table?


  • We have hundreds of similar tables that we need to change the primary key from columns A and B to columns A and C (if it hasnt been done already).  All current and new key columns are int32.  What does the query look like to determine the current primary key columns for a given table?

    Also, we need to create a new index on these tables but only if it doesn't already exist.  What does the query look like to see if a given single column index exists for a specified table?


    Friday, November 16, 2012 5:27 PM


  • DECLARE @tableName VARCHAR(128);
    SET @tableName = 'YourTable';
    SELECT * FROM sys.key_constraints WHERE type = 'PK'
    AND parent_object_id = OBJECT_ID(@tableName);

    This should give you a start.
    • Marked as answer by scott_m Friday, November 16, 2012 8:59 PM
    Friday, November 16, 2012 8:27 PM

All replies