Welcome to WebmasterWorld Guest from 100.25.214.89

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:963
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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members