THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Script to create all primary keys

Here's a script that generates a script to drop or create all of the existing primary keys in a database. There are similar published scripts, but the ones I tried had bugs. If you find any errors in this script, let me know and I'll fix it. Thanks to reader MontyMole for suggesting using QUOTENAME instead of brackets inside string constants. Also, he informed me that the original ascending/descending sort order code was wrong. Reader mitkodi found an error in the ColumnCursor. Both are corrected now. Greg Larsen submitted a fix to restrict by object type. Adriaan Van Bauwel submitted a fix for scripting clustered indexes. This code has not been tested on a database with partitioned tables or indexes, nor was any consideration given to partitioning when developing the script.

 Comments are disabled on this post. The master copy of this script now resides on the TechNet Wiki at make corrections to or find the latest version of this script, please visit the TechNet Wiki.

DECLARE @object_id int;

DECLARE @parent_object_id int;



DECLARE @is_descending_key bit;


DECLARE @action CHAR(6);


--SET @action = 'DROP';

SET @action = 'CREATE';



    select kc.object_id, kc.parent_object_id

    from sys.key_constraints kc

    inner join sys.objects o

    on kc.parent_object_id = o.object_id

    where kc.type = 'PK' and o.type = 'U'

    and not in ('dtproperties','sysdiagrams')  -- not true user tables

    order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))



OPEN PKcursor;

FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;




    IF @action = 'DROP'

        SET @TSQL = 'ALTER TABLE '

                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))

                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))

                  + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))



        SET @TSQL = 'ALTER TABLE '

                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))

                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))

                  + ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))

                  + ' PRIMARY KEY'

                  + CASE INDEXPROPERTY(@parent_object_id


                        WHEN 1 THEN ' CLUSTERED'

                        ELSE ' NONCLUSTERED'


                  + ' (';


        DECLARE ColumnCursor CURSOR FOR

            select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key

            from sys.indexes i

            inner join sys.index_columns ic

            on i.object_id = ic.object_id and i.index_id = ic.index_id

            where i.object_id = @parent_object_id

            and = OBJECT_NAME(@object_id)

            order by ic.key_ordinal;


        OPEN ColumnCursor;


        SET @col1 = 1;


        FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;

        WHILE @@FETCH_STATUS = 0


            IF (@col1 = 1)

                SET @col1 = 0


                SET @TSQL = @TSQL + ',';



                      + ' '

                      + CASE @is_descending_key

                            WHEN 0 THEN 'ASC'

                            ELSE 'DESC'



            FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;



        CLOSE ColumnCursor;

        DEALLOCATE ColumnCursor;


        SET @TSQL = @TSQL + ');';






    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;



CLOSE PKcursor;


Published Wednesday, September 16, 2009 3:22 PM by John Paul Cook



rjbook said:

This is fantastic...I was looking for a script with this exact functionality today and was glad to come across this post. One modification I made was to include the GO batch terminator after every line, this way if the script does not find a table on target or the table already has a PK in place it can continue applying the rest. Great job my good man!


September 17, 2009 3:48 PM


Function OBJECT_SCHEMA_NAME is available in Service Pack 2 of SQL Server 2005. If someone have not installed service pack 2 then will get error message "'OBJECT_SCHEMA_NAME' is not a recognized built-in function name."

July 8, 2010 7:42 AM

Chris said:

Just wanted to say thanks, very useful, very accurate script. Thanks! Saved me tons of time.

February 22, 2011 6:05 AM
New Comments to this post are disabled

About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog


Privacy Statement