Alter SQL Server table column width with indexes
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
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.