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

问题描述

我的 SQL Server 2008 出现问题.

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.

有什么方法可以从编程的角度获取所有涉及该列的索引并删除它们，然后在 alter table 语句之后自动重新创建它们?

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.

我正在从 tinyint 更改为 smallint 类型.

I'm changing from tinyint to smallint type.

推荐答案

禁用目标表上的所有索引

DISABLE all indexes on destination table

  ALTER INDEX Indexname ON Table DISABLE

然后改变列的数据类型

ALTER TABLE table
ALTER COLUMN columnname datatype

之后启用索引

ALTER INDEX Indexname ON Table REBUILD

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