Search This Blog

Code to Generate the ALTER INDEX / DISABLE Command Statements 

  1. Disable all the table’s nonclustered indexes so that they aren’t automatically rebuilt when the clustered index is dropped in step 3. Because this is likely to be a one-time operation, use the query in Listing 1 (with the desired table name) to generate the ALTER INDEX statements.
  2. Listing 1: Code to Generate the ALTER INDEX Statements
    SELECT
            DISABLE_STATEMENT =
                    N'ALTER INDEX '
                    + QUOTENAME(si.[name], N']')
                    + N' ON '
                    + QUOTENAME(sch.[name], N']')
                    + N'.'
                    + QUOTENAME(OBJECT_NAME(so.[object_id]), N']')
                    + N' DISABLE'
            , ENABLE_STATEMENT =
                    N'ALTER INDEX '
                    + QUOTENAME(si.[name], N']')
                    + N' ON '
                    + QUOTENAME(sch.[name], N']')
                    + N'.'
                    + QUOTENAME(OBJECT_NAME(so.[object_id]), N']')
                    + N' REBUILD'
    FROM sys.indexes AS si
            JOIN sys.objects AS so
                    ON si.[object_id] = so.[object_id]
            JOIN sys.schemas AS sch
                    ON so.[schema_id] = sch.[schema_id]
    WHERE si.[object_id] = object_id('tablename')
            AND si.[index_id] > 1
    Note that you should use the column for DISABLE_STATEMENTS to disable the nonclustered indexes, and be sure to keep the enable information handy because you’ll need it to rebuild the nonclustered indexes after you’ve created the new clustered index.
  3. Disable any foreign key constraints. This is where you want to be careful if there are users using the database. In addition, this is also where you might want to use the following query to change the database to be restricted to only DBO use:
ALTER DATABASE DatabaseName
SET RESTRICTED_USER
WITH ROLLBACK AFTER 5
The ROLLBACK AFTER n clause at the end of the ALTER DATABASE statement lets you terminate user connections and put the database into a restricted state for modifications. As for automating the disabling of foreign key constraints, I leveraged some of the code from sp_fkeys and significantly altered it to generate the DISABLE command (similarly to how we did this in step 1 for disabling nonclustered indexes), which Listing 2 shows.
Listing 2: Code to Generate the DISABLE Command
SELECT
    DISABLE_STATEMENT =
                N'ALTER TABLE '
                + QUOTENAME(convert(sysname, schema_name(o2.schema_id)), N']')
                + N'.'
                + QUOTENAME(convert(sysname, o2.name), N']')
                + N' NOCHECK CONSTRAINT '
                + QUOTENAME(convert(sysname, object_name(f.object_id)), N']')
    , ENABLE_STATEMENT =
                N'ALTER TABLE '
                + QUOTENAME(convert(sysname, schema_name(o2.schema_id)), N']')
                + N'.'
                + QUOTENAME(convert(sysname, o2.name), N']')
                + N' WITH CHECK CHECK CONSTRAINT '
                + QUOTENAME(convert(sysname, object_name(f.object_id)), N']')
        , RECHECK_CONSTRAINT =
                N'SELECT OBJECTPROPERTY(OBJECT_ID('
                + QUOTENAME(convert(sysname, object_name(f.object_id)), N'''')
                + N'), ''CnstIsNotTrusted'')'
FROM
    sys.objects AS o1,
    sys.objects AS o2,
    sys.columns AS c1,
    sys.columns AS c2,
    sys.foreign_keys AS f
                INNER JOIN sys.foreign_key_columns AS k
                        ON (k.constraint_object_id = f.object_id)
                INNER JOIN sys.indexes AS i
                        ON (f.referenced_object_id = i.object_id
                                AND f.key_index_id = i.index_id)
WHERE
    o1.[object_id] = object_id('tablename')
        AND i.name = 'Primary key Name'
        AND o1.[object_id] = f.referenced_object_id
        AND o2.[object_id] = f.parent_object_id
        AND c1.[object_id] = f.referenced_object_id
        AND c2.[object_id] = f.parent_object_id
        AND c1.column_id = k.referenced_column_id
        AND c2.column_id = k.parent_column_id
ORDER BY 1, 2, 3
Use the column for DISABLE_STATEMENTS to disable the foreign key constraints, and keep the remaining information handy because you’ll need it to reenable and recheck the data, as well as verify the foreign key constraints after you’ve recreated the primary key as a unique nonclustered index.