Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mihirp
Starting Member
1 Post |
 Posted - 2007-11-14 : 07:32:12
|
| Hello,I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.I have the command ready which is:ALTER TABLE tablename ALTER COLUMN columnname BIGINTThe problem happening is that it seems there are constraints across all the columns in every tables.The error message is:Server: Msg 5074, Level 16, State 1, Line 1The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.Server: Msg 4922, Level 16, State 1, Line 1ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-14 : 08:08:42
|
| you can't. your best bet would be to script all the constraints, drop them all, change the datatype and recreate the constraints._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
csundaresh
Starting Member
5 Posts |
Posted - 2007-11-14 : 09:49:23
|
There is no easy way to work this one. You will need to drop all constraints and recreate them.quote:
Originally posted by mihirp
Hello,I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.I have the command ready which is:ALTER TABLE tablename ALTER COLUMN columnname BIGINTThe problem happening is that it seems there are constraints across all the columns in every tables.The error message is:Server: Msg 5074, Level 16, State 1, Line 1The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.Server: Msg 4922, Level 16, State 1, Line 1ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.
|
|
|
|
|
|
|
|
|
Change datatype from varchar to bigint not working