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

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 2

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 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

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

Answer 6

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.