forum

Home / DeveloperSection / Forums / Alter SQL Server table column width with indexes

Ask Question

Alter SQL Server table column width with indexes

marcel ethan198406-May-2013
Hi Expert!

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

Thanks in advance!
mssql servermssql server 
Updated on 06-May-2013
marcel ethan

marcel ethan

Follow

Can you answer this question?

Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

06-May-2013
Hi Marcel!

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.

MindStick Training

Liked By