Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from programmers.stackexchange.com Apr 13 '11 at 21:55

This question came from our site for professional programmers interested in conceptual questions about software development.

    
Did you solve your problem ? –  Nico Apr 15 '11 at 21:50

3 Answers 3

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

share|improve this answer
1  
Great thanks! Do you know why this doesn't work? Declare @SqlToGetIndexName varchar(1000) Declare @indexname varchar(200) Set @SqlToGetIndexName = '(select idx.name indexName 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 –  user1480 Apr 14 '11 at 16:57
    
where idx.type <> 0 and tbl.name = ''MyTableName'' and col.name = ''MyColumnName'' and idx.name like ''%UQ%'' group by idx.name, tbl.name go)' Set @indexname = exec @SqlToGetIndexName Exec 'alter table [dbo].[MyTableName] drop ' + @indexname –  user1480 Apr 14 '11 at 16:58
    
I just update my answer to put a variable to get the drop statement. You just have to uncomment the 'exec sp_executeSql' to execute the drop statement. –  Nico Apr 14 '11 at 21:17
    
You cannot put the result of an EXEC into a variable. You must use the sp_executeSql to execute the code inside your variable. If you want to get back a value from your variable you must declare the variable inside the sp_executeSql use this link to help you with sp_executeSql msdn.microsoft.com/en-us/library/ms188001.aspx –  Nico Apr 14 '11 at 21:19

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.

share|improve this answer

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.

share|improve this answer
    
Are you sure? I tried: create table dbo.tab1main (id int identity(1,1) primary key, ts datetime); go create unique index tab1_ts on tab1main(ts); go ALTER TABLE tab1main NOCHECK CONSTRAINT tab1_ts; ---------------------------------------------------- Msg 4917, Level 16, State 0, Line 1 Die 'tab1_ts'-Einschränkung ist nicht vorhanden. Msg 4916, Level 16, State 0, Line 1 Die Einschränkung konnte nicht aktiviert oder deaktiviert werden. Siehe vorherige Fehler. –  bernd_k Apr 20 '11 at 18:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.