Solved

Alter fields with indexes

Posted on 2003-12-04
11
736 Views
Last Modified: 2012-08-14
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.



Regards

Jayesh
0
Comment
Question by:jayeshshah
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:kathmacmahon
ID: 9874479
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])
0
 
LVL 34

Expert Comment

by:arbert
ID: 9874653
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...
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9874862
I do this like arbert says.  
0
 
LVL 5

Author Comment

by:jayeshshah
ID: 9880109
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.

kathmacmahon
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.

arbert
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.

K'Regards

Jayesh



0
 
LVL 34

Expert Comment

by:arbert
ID: 9880196
"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...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:jayeshshah
ID: 9880393
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.

K'Regards
Jayesh


0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 9880449
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).

0
 
LVL 3

Expert Comment

by:kathmacmahon
ID: 9881439
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
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9881878
I also concur with arbert
0
 
LVL 5

Author Comment

by:jayeshshah
ID: 10005447
sorry for closing this question late .... i also agree to arbert and have done exactly as per what he had mentioned in his post ...

Regards

Jayesh
0
 
LVL 34

Expert Comment

by:arbert
ID: 10005642
Thanks for the followup Jayesh!

Brett
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now