SQL Server删除并重新创建表的索引

I have a situation in my SQL Server 2008.

我的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.

我需要更改列类型,但索引阻止了更改。但是由于数据库位于多个客户端上,我不知道涉及该列的索引有多少。

There is any way of get, programatically speaking, all indexes that involves the column and drop them, and after the alter table statement recreate them automatically?

从程序上讲,有任何方法可以获取涉及列并删除它们的所有索引,并在alter table语句自动重新创建它们之后?

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

我听说禁用它们会使表格混乱,因为类型的变化。

I'm changing from tinyint to smallint type.

我正在从tinyint变为smallint类型。

6 个解决方案

#1

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

#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

#3

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

希望这可以帮助...

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

让我们假设基本情况(列不是任何约束的一部分,而不是具有XML索引的XML列等),可以完成以下操作:

• generate indexes descriptions as an XML using select (...) from <sys.indexes + other sys schema views> FOR XML ...

使用 FOR XML中的select(...)生成索引描述为XML ...

• put each XML as extended property of a table naming it e.g. with a prefix 'IX_' ('IX_1', 'IX_2', etc...)

将每个XML作为表的扩展属性,例如将其命名为带有前缀'IX_'('IX_1','IX_2'等...)

• drop indexes
• alter column
• gather all extended properties of a table having prefix 'IX_'

收集前缀为“IX_”的表的所有扩展属性

• recreate each index based on its XML description

根据XML描述重新创建每个索引

#5

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

您可以使用内置工具完成此任务。在SQL Server Management Studio中,单击“工具”,然后单击“选项”

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

展开“SQL Server对象资源管理器”集,然后单击“脚本”。

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.

找到名为“脚本索引”的记录并将其设置为“True”,然后单击“确定”。

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.

当您在对象资源管理器中右键单击表时,您可以选择“脚本为...”选择这些选项中的任何一个,现在将编写索引以及表本身及其键的脚本。复制所需的脚本,或者根据需要运行整个过程。

#6

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

下面是一些安全删除和重新创建索引的示例SQL:

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:

这里有一些C#代码可以解决这个问题:

    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:

这里有一些C#(可以转换为SQL)来获取索引模式:

            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.

所以,基本上,你在这里执行最后一段代码,遍历结果(索引和列)并为返回的每个索引调用GetCreateIndexScript。然后,您可以安全地执行为删除和重新创建索引而创建的每个语句。

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

这种方法可以与TSQL或其他语言一起使用。