SQL Server 删除和重新创建表的索引 [英] SQL Server drop and recreate indexes of a table

问题描述

我有一种情况，我的SQL Server 2008中.

我需要改变一个列类型，但索引防止更改.但由于数据库是在几个客户，我不知道有多少指标如何存在涉及列.

有没有得到，编程而言，涉及该列，并将其丢弃所有索引的任何方式，并在 ALTER TABLE 语句创建这些自动?

后

我听说禁用它们可以乱用表，因为类型的变化.

我从TINYINT到SMALLINT类型改变.

解决方案

也可以尝试这个就知道餐桌上的所有索引列名:

<预> <代码> 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从SYS.INDEXES INDINNER JOIN sys.index_columns ICON ind.object_id = ic.object_id和ind.index_id = ic.index_idINNER JOIN SYS.COLUMNS山坳ON ic.object_id = col.object_id和ic.column_id = col.column_idINNER JOIN SYS.TABLESŧON ind.object_id = t.object_idWHERE t.is_ms_shipped = 0ORDER 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

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.

解决方案

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

这篇关于SQL Server 删除和重新创建表的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！