Solved

Alter fields with indexes

Posted on 2003-12-04
11
741 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
Zoho SalesIQ

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

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

920 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

15 Experts available now in Live!

Get 1:1 Help Now