Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / alter sql server table column width with indexes
Ask Question

Alter SQL Server table column width with indexes

marcel ethan 2340 06 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 server mssql server
Updated 06 May 2013
marcel ethan
marcel ethan

Other

Message

1 Answers

AVADHESH PATEL
AVADHESH PATEL 06 May 2013
Report Abuse
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.

advertising