SQL Server drop and recreate indexes of a table

Question

I have a situation in my SQL Server 2008.

I need to change a column type, but the indexes are preventing the changes. But because of the database is on several clients, I don't know how many indexes exists involving the column.

Is there any way of getting, programmatically speaking, all indexes that involve the column and drop them, and after the alter table statement recreate them automatically?

I've heard that disabling them can mess with the table because of the change of type.

I'm changing from tinyint to smallint type.

Answer 1

Here's what I came up with to drop / create / disable / enable (rebuild) Microsoft SQL Server indexes:

CREATE VIEW dbo.vw_INDEX_TEXT AS
  SELECT
    x.[owner_name],
    x.[table_name],
    x.[index_name],
    
    /*
    ** DROP index
    */
    drop_sql = 'DROP INDEX ' + x.[index_name] + ' ON ' + x.[owner_name] + '.' + x.[table_name] + ';',

    
    /*
    ** CREATE index
    */
    create_sql = 
     'CREATE NONCLUSTERED INDEX ' + x.[index_name] + '
        ON ' + x.[owner_name] + '.' + x.[table_name] + '(' + 
          -- the common-delimited field list. 
         substring(
                (SELECT
                  ',' + [field_name] 
                FROM
                  (
                  SELECT 
                    [owner_name] = SCHEMA_NAME(O.schema_id),
                    [table_name] = O.name,
                    [index_name] = I.name,
                    [field_name] = C.name,
                    O.type,
                    S.key_ordinal,
                    S.is_descending_key,
                    S.is_included_column 
                  FROM 
                    sys.all_objects O inner join sys.indexes I       ON (O.object_id = I.object_id )
                                      inner join sys.index_columns S ON (O.object_id = S.object_id and I.index_id=S.index_id)
                                      inner join sys.columns C       ON (O.object_id = C.object_id and S.column_id = C.column_id)
                  WHERE 
                    I.index_id > 0 
                    AND SCHEMA_NAME(O.schema_id) = x.[owner_name] -- N'dbo' 
                    AND I.name                   = x.[index_name] -- the index name N'IDX_myindex' 
                    AND O.name                   = x.[table_name] -- the base table name N'mytable' 
                    AND O.type                   <> 'IT' 
                    AND I.is_primary_key         = 0 -- we are not creating primary keys  
                    AND I.is_unique_constraint   = 0 -- we are not creating unique constraints
                    AND (INDEXPROPERTY(I.object_id,I.name,'IsStatistics') <> 1)
                    AND (INDEXPROPERTY(I.object_id,I.name,'IsAutoStatistics') <> 1) 
                    AND (INDEXPROPERTY(I.object_id,I.name,'IsHypothetical') <> 1) 
                  ) as f
                FOR XML PATH('') 
            ), 2, 8000) -- we trim the leading comma 
            -- end of field list
        + ') 
     WITH (
          PAD_INDEX = OFF
         );',
         
    /*
    ** DISABLE index
    */
    disable_sql = 'ALTER INDEX ' + x.[index_name] + ' ON ' + x.[owner_name] + '.' + x.[table_name] + ' DISABLE;',

    /*
    ** ENABLE (REBUILD) index
    */
    enable_sql = 'ALTER INDEX ' + x.[index_name] + ' ON ' + x.[owner_name] + '.' + x.[table_name] + ' REBUILD;'

         
  FROM
    (SELECT 
      [owner_name] = SCHEMA_NAME(O.schema_id),
      [table_name] = O.name,
      [index_name] = I.name
      -- other interesting, but not used fields that might be useful for other index types:
      -- O.type,I.index_id,I.is_unique,
      -- prop= INDEXPROPERTY(I.object_id,I.name,'IsClustered'),
      -- I.is_padded,
      -- I.fill_factor,
      -- I.ignore_dup_key,I.allow_row_locks,I.allow_page_locks,I.is_disabled,I.data_space_id 
    FROM 
      sys.all_objects O INNER JOIN sys.indexes I on O.object_id=I.object_id 
    WHERE 
      I.index_id>0 
      -- AND O.name = @target_table_name
      AND O.type <> 'IT' 
      AND INDEXPROPERTY(I.object_id,I.name,'IsStatistics')     <> 1 
      AND INDEXPROPERTY(I.object_id,I.name,'IsAutoStatistics') <> 1 
      AND INDEXPROPERTY(I.object_id,I.name,'IsHypothetical')   <> 1 
      AND I.is_primary_key = 0 
      AND I.is_unique_constraint = 0  
    ) as x

Only the most basic indexes are recreated, but I left some extra fields that would be useful as a basis for creating more complex indexes as needed.

Commands are in each field of the view. Use exec to actually perform the operation like this (put in your table name for @target_table_name):

  Declare @target_table_name as sysname = 'mytable';
  Declare @sql_cmd           as table(ID int identity, cmd varchar(max) );
  Declare @this_cmd          as varchar(max) = '';
  Declare @ct                as int = 0;
  
  -- populate a temp table to store the results
  INSERT INTO @sql_cmd(cmd)
    SELECT
      cmd = enable_sql
    FROM
      dbo.vw_INDEX_TEXT 
    WHERE 
      table_name = @target_table_name
 
  -- the ID column will help us step though the rows one at a time
  SELECT @ct = max(ID) FROM @sql_cmd

  -- loop over all rows in the table, finding each individual command
  While (@ct > 0) Begin
    SELECT
      @this_cmd = cmd
    FROM
      @sql_cmd
    WHERE
      id = @ct
      
    select @this_cmd

    -- un-comment this line to actually run the command:
    -- exec (@this_cmd)
    
    SET @ct = @ct - 1
  End
    

Note the exec is disabled in the example.

Answer 2

Here is some example SQL of dropping and recreating an index safely:

IF(select object_id from sys.indexes  where [name] = 'IDX_RecordSubscription' and object_id = OBJECT_ID('[SystemSetup].[RecordSubscription]')) IS NOT NULL 
BEGIN 
    DROP INDEX [SystemSetup].[RecordSubscription].IDX_RecordSubscription 
 END

GO

CREATE  UNIQUE  INDEX
    IDX_RecordSubscription
ON
    [SystemSetup].[RecordSubscription]
    (
            Subscriber ASC,
    MenuItem ASC,
    RecordPrimaryKeyGuid ASC

    )
    WITH
    (
        PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF
    ) ON [PRIMARY] 

GO

Here is some C# code that pumps this out:

    protected override string GetCreateIndexScript(string uniquePart, string indexName, string fullTableName, string columnsPart)
    {
        return
            $"IF(select object_id from sys.indexes  where [name] = '{indexName}' and object_id = OBJECT_ID('{fullTableName}')) IS NOT NULL \r\n" +
            "BEGIN \r\n" +
            $"    DROP INDEX {fullTableName}.{indexName} \r\n " +
            "END\r\n\r\n" +
            "GO\r\n\r\n" +
            $"CREATE {uniquePart} INDEX\r\n" +
            $"\t{indexName}\r\n" +
            "ON\r\n" +
            $"\t{fullTableName}\r\n" +
            "\t(\r\n" +
            $"\t\t{columnsPart}\r\n" +
            "\t)\r\n" +
            "\tWITH\r\n" +
            "\t(\r\n" +
            "\t\tPAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF\r\n" +
            "\t) ON [PRIMARY] ";
    }

Here's some C# (that can be converted to SQL) to get the index schema:

            const string selectStatement = "select " +
                                           "    SCHEMAs.name + '.' + tabs.name as OBJECT_ID,  " +
                                           "    ind.name as INDEX_NAME,  " +
                                           "    indcol.index_id AS INDEX_ID,  " +
                                           "    indcol.key_ordinal AS COLUMN_ORDINAL,  " +
                                           "    col.name AS COLUMN_NAME,  " +
                                           "    ind.is_unique " +
                                           "from " +
                                           "    sys.indexes ind " +
                                           "inner join " +
                                           "    sys.index_columns indcol     " +
                                           "on " +
                                           "    ind.object_id = indcol.object_id and " +
                                           "    ind.index_id = indcol.index_id " +
                                           "inner join " +
                                           "    sys.columns col " +
                                           "on " +
                                           "    col.object_id = indcol.object_id and " +
                                           "    col.column_id = indcol.column_id " +
                                           "inner join " +
                                           "    sys.tables tabs " +
                                           "on  " +
                                           "    tabs.object_id = ind.object_id " +
                                           "inner join " +
                                           "   sys.schemas schemas " +
                                           "on " +
                                           "   tabs.schema_id = schemas.schema_id " +
                                           "where  " +
                                           "    ind.type =2 and" +
                                           "   tabs.name <> 'sysdiagrams' " +
                                           "order by " +
                                           "    tabs.object_id, " +
                                           "    indcol.index_id, " +
                                           "    indcol.key_ordinal ";

            return DatabaseAdapter.Fill(selectStatement, null, null, null);

So, basically, you execute the last piece of code here, iterate through the results (indexes and columns) and call GetCreateIndexScript for each index that is returned. Then you can safely execute each of the statements that are created to drop and recreate the indexes.

This same approach could be used with TSQL, or another language.

Answer 3

You can use the built-in tools to do this job. In SQL Server Management Studio, click "Tools" then "Options"

Expand the "SQL Server Object Explorer" set and Within that, click "Scripting".

Scroll down to the "Table And View Options" on the right hand side.

Find the record called "Script Indexes" and set it to "True", then click OK.

When you right click your table in the Object Explorer, you have options to "Script As..." selecting any of these options will now script out the indexes as well as the table itself and its keys. Copy the required scripts, or just run the whole thing depending on your need.

Answer 4

Lets assume basic case (column is not a part of any constraint, not an XML column having XML index over it, etc.), the following can be done:

• generate indexes descriptions as an XML using select (...) from <sys.indexes + other sys schema views> FOR XML ...
• put each XML as extended property of a table naming it e.g. with a prefix 'IX_' ('IX_1', 'IX_2', etc...)
• drop indexes
• alter column
• gather all extended properties of a table having prefix 'IX_'
• recreate each index based on its XML description

Answer 5

Also try this to know the all the indexes on table with column names:

SELECT  OBJECT_SCHEMA_NAME(ind.object_id) AS SchemaName
      , OBJECT_NAME(ind.object_id) AS ObjectName
      , ind.name AS IndexName
      , ind.is_primary_key AS IsPrimaryKey
      , ind.is_unique AS IsUniqueIndex
      , col.name AS ColumnName
      , ic.is_included_column AS IsIncludedColumn
      , ic.key_ordinal AS ColumnOrder
FROM    sys.indexes ind
        INNER JOIN sys.index_columns ic
            ON ind.object_id = ic.object_id
               AND ind.index_id = ic.index_id
        INNER JOIN sys.columns col
            ON ic.object_id = col.object_id
               AND ic.column_id = col.column_id
        INNER JOIN sys.tables t
            ON ind.object_id = t.object_id
WHERE   t.is_ms_shipped = 0
ORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName
      , OBJECT_NAME(ind.object_id) --ObjectName
      , ind.is_primary_key DESC
      , ind.is_unique DESC
      , ind.name --IndexName
      , ic.key_ordinal

Answer 6

You can use below script which returns Index Name and Type for specified Table/Column.:

DECLARE @tableName SYSNAME
DECLARE @columnName SYSNAME

SET @tableName = 'Products'
SET @columnName = 'Name'

SELECT  IDX.name, IDX.type_desc, IndexedColumn
FROM    sys.tables TBL
INNER JOIN  sys.indexes IDX ON TBL.object_id = IDX.object_id
LEFT JOIN   sys.filegroups FG ON IDX.data_space_id = FG.data_space_id
CROSS APPLY
(   SELECT  COLS.Name
    FROM    sys.index_columns IXCL
    INNER JOIN  sys.columns COLS
                ON IXCL.object_id = COLS.object_id
                AND IXCL.column_id = COLS.column_id
    WHERE   IDX.object_id = IXCL.object_id
    AND     IDX.index_id = IXCL.index_id
    AND     COLS.name = @columnName
    AND     IDX.object_id = OBJECT_ID(@tableName)
) Indexed (IndexedColumn)
WHERE   TBL.object_id = OBJECT_ID(@tableName)

Hope This Helps...

Answer 7

DISABLE all indexes on destination table

  ALTER INDEX Indexname ON Table DISABLE

Then Alter datatype of a column

ALTER TABLE table
ALTER COLUMN columnname datatype

After that Enable Indexes

ALTER INDEX Indexname ON Table REBUILD