Alter SQL Server table column width with indexes

Question

I am using SQL Server 2008 and need to alter a large number of columns across many tables from decimal(9,3) to decimal(12,6)

The issue I currently have is that some tables have several indexes on these columns and the alter statement fails due to the index.

Is there a way to alter the column without losing the index?

I am altering the column as follows:

alter table [TABLE_NAME] alter column [Conf_Tonnes] decimal(12,6) not  null

Answer 1

Quoting Microsoft:

ALTER COLUMN cannot have a collation change if one or more of the following conditions exist:

• If a CHECK constraint, FOREIGN KEY constraint, or computed columns
  reference the column changed.

• If any index, statistics, or full-text index are created on the
  column. Statistics created
  automatically on the column changed
  are dropped if the column collation
  is changed.

• If a schema-bound view or function references the column.

I'm afraid you need to drop and recreate the indexes.

Answer 2

I believe it is not possible to change the type of a column whilst it has any constraint on it. Certainly it used to be the case with earlier versions of SQL Server, and I don't think it has changed.

For practical purposes, you can use a script to list all fields of a certain type:

DECLARE @name AS varchar(20)

SET @name = '<Name of type>'

select T.name as "Table", F.name as "Field" 
from sys.tables T left join sys.columns F on T.object_id=F.object_id
where F.user_type_id=(select user_type_id from sys.types where name=@name)

Which will give you the list of fields which need changing. You can also drop constraints from fields but the difficult thing is how to recreate them. if you have external meta-descriptions of the database, then you can use that to generate scripts easily. Alternatively, you could run the script generate tool - select all tables on, all options off, except tables and indexes - this should generate the full list of tables and indexes for you. You can find it by right-clicking on the database in object explorer/tasks/generate scripts.

Unfortunately I don't think you can get index scripts generated without having table create scripts created as well - but Visual Studio text editing scripts shoudl make the job of cutting out the bits you don't want fairly easy.

Given time, it's probably possible to put together some scripts to do the whole job automatically, and it would give you a decent set of tools for future use.