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!
Best Answer
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
Hope this will help you