Dependency error while altering column length

[bmacbmac]

Hello,

I have about 150 databases where I need to change the a stownship column from char(7) to char(3). I am running

alter table tblland alter column stownship char(3)

But I get the following error:

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


Is there a way I can do this with SQL queries? I can alter the column length in enterprise manager just fine. It gives me some messages, but still allows it to go through.

I'm hoping to avoid doing this in enterprise manager and just run queries on all of the databases. Is there a way around these erors?

Thanks!

Brian

 

[pjw001]

You could try dropping the references to the column first and then adding them back after the alter.
This could of course lead to further problems if there are any values longer than 3 characters.

 

[Qik3Coder]

Do you really need to downsize the column?
Or is restricting the input enough? If you have a significant number of tables with indexes and foreign keys this is going to be a nightmare.

Your going to need generic sql to identify your dependencies to remove/add them.

Lodlaiden

PS: A wallet that holds 7 20's can also hold 3 20's just fine.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[gmmastros]

PS: A wallet that holds 7 20's can also hold 3 20's just fine.

Sometimes I don't have 2 nickels to rub together. Will query for food.

(just kidding)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

I was going to use a sack and gold bars (or kittens) (or gold bars and kittens) but figured I'd keep it simple.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD