SQL Server drop and recreate indexes of a table

sql, sql-server, sql-server-2008, sql-server-2008-r2

Solution

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

Problem

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.

Original source