THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 http://social.technet.microsoft.com/wiki/contents/articles/script-to-create-or-drop-all-primary-keys.aspx.To 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 @TSQL NVARCHAR(4000);

DECLARE @COLUMN_NAME SYSNAME;

DECLARE @is_descending_key bit;

DECLARE @col1 BIT;

DECLARE @action CHAR(6);

 

--SET @action = 'DROP';

SET @action = 'CREATE';

 

DECLARE PKcursor CURSOR FOR

    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 o.name not in ('dtproperties','sysdiagrams')  -- not true user tables

    order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))

            ,QUOTENAME(OBJECT_NAME(kc.parent_object_id));

 

OPEN PKcursor;

FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;

  

WHILE @@FETCH_STATUS = 0

BEGIN

    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))

    ELSE

        BEGIN

        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

                                      ,OBJECT_NAME(@object_id),'IsClustered')

                        WHEN 1 THEN ' CLUSTERED'

                        ELSE ' NONCLUSTERED'

                    END

                  + ' (';

 

        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 i.name = 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

        BEGIN

            IF (@col1 = 1)

                SET @col1 = 0

            ELSE

                SET @TSQL = @TSQL + ',';

 

            SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)

                      + ' '

                      + CASE @is_descending_key

                            WHEN 0 THEN 'ASC'

                            ELSE 'DESC'

                        END;

 

            FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;

        END;

 

        CLOSE ColumnCursor;

        DEALLOCATE ColumnCursor;

 

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

 

        END;

 

    PRINT @TSQL;

 

    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;

END;

 

CLOSE PKcursor;

DEALLOCATE PKcursor;

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

Comments

 

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!

-RJ

September 17, 2009 3:48 PM
 

SHASHIKANT said:

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 both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft 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. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement