Sql-server – Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered

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