Dropping a constraint (index) on a column

Question

How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had dependencies on the column (which turned out to be indexes).

I was able to easily get around this locally by right clicking the index and scripting a drop, but I need to roll this out on other servers where I won’t have access to the index name.

Is there a way I can make a script that will drop any index, let me make that datatype change on the column then read the index? Thanks!

Answer 1

You can use sys.indexes view to get indexes. You can join this table to sys.tables, sys.columns and sys.index_column to get informations to create a dynamic drop index. You can use this simple select to generate your drop index. You can use the where clause to filter the tables and the columns. If you want to change Table1.Column1 you must use those name to filter the select to get the right delete statement

use [testdb]
go
declare @sqlDropIndex NVARCHAR(1000)

select @sqlDropIndex = 'DROP INDEX ' + idx.name + ' ON ' + tbl.name
from sys.indexes idx inner join 
        sys.tables tbl on idx.object_id = tbl.object_id inner join
        sys.index_columns idxCol on idx.index_id = idxCol.index_id inner join
        sys.columns col on idxCol.column_id = col.column_id
where idx.type <> 0 and
        tbl.name = 'MyTableName' and
        col.name = 'MyColumnName'
group by idx.name, tbl.name
order by idx.name desc

print @sqlDropIndex
--exec sp_executeSql @sqlDropIndex
go

Hope this will help you

Answer 2

In order to alter any column, first you have to drop the index or any other constraints that contain that column, and after create the index/constraint again. But this task must be done by a DBA because any drop on index or constraint will affect on one hand the data being queried - while the index is dropped, and on other hand put blockage on whole table for the time that the index is re-created. You need to make sure the users will be aware of this little or big (depends on the table size) maintenance. Good luck!

However the Index create can be done with Online option which is less blocking.

Answer 3

If you drop the index first, you will not be able to read from it again, unless the index is recreated. What you can do it to disable the enforcement of the constraint.

ALTER TABLE name_of_the_table NOCHECK CONSTRAINT name_of_the_constraint;

After this script is run, the constraint will not be enforced, and you can enter data without worrying about the constraint checks. You can alter the table again, to re-enable the constraints.

ALTER TABLE name_of_the_table CHECK CONSTRAINT name_of_the_constraint;

Any previous violations will not be checked or corrected, but any entries after the constraint has been enforced, will be checked.

Answer 4

There are 2 mistakes in Niko's answer (2 object_id conditions missing). And no need to group by:

SELECT  @sql = 'DROP INDEX ' + idx.name + ' ON ' + tbl.name
FROM    sys.indexes             idx 
INNER JOIN sys.tables           tbl     ON idx.object_id = tbl.object_id 
INNER JOIN sys.index_columns    idxCol  ON idx.index_id = idxCol.index_id   AND idx.object_id = idxCol.object_id 
INNER JOIN sys.columns          col     ON idxCol.column_id = col.column_id AND  idxCol.object_id = col.object_id
WHERE   idx.type <> 0 
AND     tbl.name = 'file_transfer_log' 
AND     col.name = 'tender_id';