I have to extend the length of a field, which appears in hundred of tables in a database, and maybe involved in constraints/index/primary key. It is difficult for me to drop all the constraints/index/primary key before altering each table one by one.....Is there any easier way to drop all constraints and keys (instead of dropping them one by one)? Or any better (faster) way to extend the length of this field?
Script out your entire database. Then use search and replace to modify all instances of the field length (character, I assume?). Then use this script to create a new, corrected database, and use DTS to transfer data from the old database.
I hope you are aware, though, that you may likely render much of your SQL code (procedures, functions, triggers...) obsolete if they reference your field and attempt to assign it or concatenate it to variables that are of insufficient length. You may have quite a debugging job ahead of you. A 3rd party package such as ERWIN might be of assistance.