Subject Re: Updating field length
Author Adam
--- In, "Paul R. Gardner"
<gardnerp@...> wrote:
> I have a VarChar field that I want to increase the length of (from 4 to
> 6). I run:
> Now I get an error because it's used in stored procedures and triggers.
> So I have two options:
> 1) Drop all stored procedures/triggers, alter the field, and finally add
> back my stored procedures/triggers

The problem with dropping dependencies is that you have to then drop
dependencies on dependencies etc, and you end up with a several
thousand line script to do very little. A much better approach is to
alter the procedure / trigger to include nothing between begin and
end, make the change then alter the procedure / trigger back.

There are many tools that can do this for you. In flamerobin, simply
go to the table properties and choose generate rebuild script. After
it has 'dropped' everything, make your change, then make whatever
changes are necessary in the rebuild side. This usually only takes a
few minutes, and although it is a bit of a pain, it is preferable to
have to go through this process then to end up with database
corruption because some change that breaks a procedure or trigger is