Welcome to WebmasterWorld Guest from 54.196.243.192

Forum Moderators: open

Message Too Old, No Replies

Change datatype from varchar to bigint not working.

     
12:29 pm on Nov 14, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 3, 2003
posts:960
votes: 0


Hello,

I am using MS-Sql 2000. 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 BIGINT

[This example is just for the columnname of 1 table, I am using multiples one on each line]

The 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 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER 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.

1:18 pm on Nov 14, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


I don't think you will find an easy solution to this. If you must have the information in BIGINT format, a compromise would be to create a virtual (computed) column with the datatype BIGINT. This virtual column would have a formula that converted the existing VARCHAR column contents to BIGINT using the CONVERT command. An added advantage is the virtual column doesn't take up any space.