Script to Create or Drop All Primary Keys

This is a script to generate create or drop statements for all primary keys in a SQL Server database. It was originally published on the original author's blog in 2009, but this is a better place to post it. The community has the ability to correct or enhance the script here. The script was not tested on a database that has partitioned tables or partitioned indexes. Hopefully someone can test it on a partitioned database and update this Wiki post if necessary.

To generate drop statements, uncomment the statement that sets @action to DROP and comment out the statement that sets @action to CREATE.

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;