Alter fields with indexes

Hi All,

I need to modify the datatypes of all the fields in my database to a new datatype.
nvarchar to varchar, decimal to numeric, datetime to smalldatetime.

I generated alter table scripts for all the tables and modified the datatypes

Now the problem i am facing is fields with indexes. I got some unique indexes set for quite a lot of fields in my database.
The Alter table statement didnot work

I get an error as below

Server: Msg 5074, Level 16, State 8, Line 1
The index 'IDX_Supplier' is dependent on column 'Supplier_Id'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN Supplier_Id failed because one or more objects access this column.

Simple solution is to drop the index on this field and re-create them but then i need to generate the scripts and update the same on the live server.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Your simple solution is the solution you need.

You can do it all in one script:

DROP INDEX tablename.IDX_Supplier
ALTER TABLE tablename ALTER COLUMN Supplier_Id                    T_NEWTYPE              NULL OR NOT NULL
CREATE  INDEX IDX_Supplier ON tablename([Supplier_Id])
If you make the change in enterprise manager, you can click the icon on the tool bar to save the sql script instead of applying the changes to the table.  You can then use this script on your other table...
I do this like arbert says.  
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

jayeshshahAuthor Commented:
This is a data table which was made quite a long time ago. now i am taking charge of the database and trying to remove all the mismatches in the database and adding proper indexes so that the application works quite fine.

For this i have got a test database on our local test server and am qworking on it. These changes in the datatypes would then need to be applied on the live Server taking into mind that my data loss should not be there.

Neither do i want to export and import data since there are object level permissions given to various users so i need to copy that also which i would not prefer.

I have got a lot of tables with indexes/pk's & FK's  i cannot go on dropping and re-creating the indexes for all these tables. frankly speaking i shall go mad doing it. I tried with only 6 tables which are the biggest in my database and i got mad after all these tables.

Generate SQL Script from the design table interface would invoke the Generate SQL script wizard for this table. And this wizard would generate SQL script for the complete database i.e. create table syntax also would be included along with drop table. Which i donot want. I only want to change my datatypes to as mentioned above in my question post.
If i drop and re-create my table then i would have to loose my data or keep a copy of the existing data and then run a DTS package manually or some other way. Which is more difficult for me.

In case of triggers or Check constraints i can disable them and then modify the values and then enable the same.

Is there something similar to it.



"Generate SQL Script from the design table interface would invoke the Generate SQL script wizard for this table."

I didn't say generate script--I said save script--they are totally different things.  The save script will script the changes and even the insert statements to insert the data into a temp table (if there are field changes) and back into the original table with the structure changed.

In any case what you explained in your last post was different from your original question--what is your question exactly????

If you really want to keep two databases and keep track of the changes (and be able to synch them up) I would highly recommend a good modeling tool like Sybase Powerdesigner or Erwin.  This task becomes pretty large without some kind of tool/change control...
jayeshshahAuthor Commented:
actually both my questions are meaning the same thing only thing is that i tried to put them up in a different way so that you could understand better. Sorry if i confused or misguided you.

Let me explain you my problem.

I have a database which is existing on the live Server on the clients end. I took a backup of this database and installed it on my machine. Now while going through this database i found a lot of mismatch in fields and un-necesscary lengths of datatypes like for e.g. In the table Supplier Master the max value for supplier name is varchar(2000) where as if i see in the data the max length of the supplier it's around 35.  as well there is a unique index in the Suppliers master but no relation exists between this table and the Orders table.
I know this is bad design but I am not the one who has done it and now i need to correct the same.

To do so i am going through each and every table and modifying the structure taking into mind that there is no dataloss. Now since i am doing this on the database existing on my machine these changes need to be applied on the production database existing on the clients server.

To do so i need to send SQL scripts to them. For basic fields i have already made the SQL scripts. But still there are many tables where i need to change the datatypes of fields which are included in indexes.  

A simple Alter table would not work here and i get an error mentioned above in my post.

To update the same is tehre any other way to modify the datatypes without deleting and recreating indexes keeping in mind i need to send scripts which would be executed on the live database.

I hope this much information is enough.


Like I said in my last post--you really only have those options.  You can NOT change a datatype that has an index built on it without dropping the index first (or if it has a constraint on it, you have to drop the constraint first).  We've all experienced this--you just have to plan it well and apply updates to the "production' environment when it poses the least amount of downtime (I've been into work many times at 3am).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arbert's right, and I'm still not sure I understand the issue here.  You indicated that you already created scripts that run the alter statements for your data type.

Can you not open those in notepad and add the drop/re-add commands as I indicated in my post?  They don't need to be separate scripts.

If you concerned about the downtime, think you will need to be making more alternations in the future or need to modify more than one DB the only other thing you might consider is writing a little DBModifier Application that uses ADO.  

You can execute all the scripted text through ADO, our system supports a small VB application that runs through the DB Changes for each release and applies them to live systems in a matter of minutes.  Including adding tables, modifying tables, adding and dropping contraints as well as populating tables.  But that's probably a bit of overkill for your needs.

It might be tedius to run the scripts, but then you should be able to compile them into one too.

Good luck, Kathleen
I also concur with arbert
jayeshshahAuthor Commented:
sorry for closing this question late .... i also agree to arbert and have done exactly as per what he had mentioned in his post ...


Thanks for the followup Jayesh!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.