Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

Question

What's a good way to easily convert a clustered index on a primary key column to a nonclustered index? I know how to do this manually, but it's time consuming if there are several tables which have foreign keys to the primary key as they need to be dropped and recreated.

Looking for a script or free tool to automate this.

I am using SQL Server 2012

Why what? Why I want a script or why I want to change the index? — Tony_Henrich, Jun 12, 2014 at 21:14
Because sql server creates a clustered index when you create a primary key and a table can have one only and I need it for another column — Tony_Henrich, Jun 12, 2014 at 23:17
You know when you create a table and you specify a column (or columns) as the PRIMARY KEY, you can say NONCLUSTERED. — Aaron Bertrand, Jun 13, 2014 at 0:33

John Eisbrener · Accepted Answer · 2014-06-12 19:45:04Z

Well, luckily for you, I did this very thing a few months back... I just never actually published it to my blog... Guess I should get to that at some point, but until then, here you go:

/***************************************************************************************************************
 *
 *  This Script will perform the following operations:
 *    1) Drop All RI
 *    2) Drop All PKEYS
 *    3) Recreate PKEYS as NONCLUSTERED
 *    4) Recreate ALL RI
 *
 *  To do this it will create a work table and populate it with the commands to execute then iterate through
 *  the work table to execute pregenerated commands.
 *
 ***************************************************************************************************************/

CREATE TABLE #workTable
(
    CommandID   INT IDENTITY(1,1),
    Command     VARCHAR(MAX)
)

SET NOCOUNT ON;

/***************************************************************************************************************
 *
 *  DROP FOREIGN KEYS
 *
 *  http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
 *
 ***************************************************************************************************************/


INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS --''');

INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');


/***************************************************************************************************************
 *
 *  DROP CLUSTERED PKEYS
 *
 *  http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS --''');

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;

    INSERT INTO #workTable (Command)
    SELECT @TSQL;

    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;

CLOSE PKcursor;
DEALLOCATE PKcursor;

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  CREATE NONCLUSTERED PKEYS
 *
 *  http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
 *
 * NOTE: Subsection where script determines if PKEY or not was modified to only create NONCLUSTERED pkeys
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS --''');

--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'
                  + ' 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 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;

    INSERT INTO #workTable (Command)
    SELECT @TSQL;

    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;

CLOSE PKcursor;
DEALLOCATE PKcursor;

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  CREATE FOREIGN KEYS
 *
 *  http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS --''');

INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')'
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

SET NOCOUNT OFF;

/***************************************************************************************************************
 *
 *  Iterate through Work Table on the database
 *
 ***************************************************************************************************************/

--SELECT * FROM #workTable
DECLARE @Command VARCHAR(MAX)

DECLARE WorkTableCursor CURSOR
FOR
    SELECT Command
    FROM #workTable

OPEN WorkTableCursor

FETCH NEXT FROM WorkTableCursor
INTO @Command

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Command
    EXECUTE (@Command)

    FETCH NEXT FROM WorkTableCursor
    INTO @Command
END

CLOSE WorkTableCursor
DEALLOCATE WorkTableCursor

DROP TABLE #workTable

Stack Exchange Network

Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
clustered-index
ddl
or ask your own question.

Hot Network Questions

Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2012clustered-indexddl or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
clustered-index
ddl
or ask your own question.