Welcome to WebmasterWorld Guest from 54.160.163.163

Forum Moderators: open

Message Too Old, No Replies

Change datatype from varchar to bigint not working.

     

Imaster

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

WebmasterWorld Senior Member 10+ Year Member



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.

syber

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

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month