>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.
Check sp_depends system stored procedure to find out where this field is used.
If it's not broken, fix it until it is.
My Blog