This script is executed as is for several other maintenances I do on various databases. However, it failed three times since yesterday saying that it cannot drop the column:

         SET @Command = N'ALTER TABLE [MyTable] ADD [HomePhone-Temporary] numeric(15,0)'
         EXEC sp_executesql @Command
         SET @Command = N'UPDATE [MyTable] SET [HomePhone-Temporary]=HomePhone'
         EXEC sp_executesql @Command
         SET @Command = N'ALTER TABLE [MyTable] DROP COLUMN [HomePhone]'
         EXEC sp_executesql @Command
         EXEC sp_RENAME 'MyTable.HomePhone-Temporary', 'HomePhone' , 'COLUMN'

The message is:

"ALTER TABLE DROP COLUMN HomePhone failed because one or more objects access this column."

Anyone would know what could cause this? I would also like to know how to prevent this.

Basically, on big tables, if we change the length of a field, we cannot do it directly on the field as it would time out. So, we have to create a temporary field, move the data into that temporary field, removed the main field and rename the temporary filed to the main field.

Unless there is a better way to do it, I would like to get some info on this on what I can do to make sure that this will always work even on big tables.