Help with ALTER COLUMN needed

[Thomas Due]

Hi,

I have a database, in which I have made a mistake regarding the datatype of several columns. So I found out that I could use

ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)

CoreWeight is originally defined as an INT.

My problem though, is that I can't.

Whenever I try this T-SQL command, I get this error:

Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column 'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects access this column.


I can change the datatype without any problems from either Enterprise Manager or the new Sql Server Management Studio Express. I am working with a MS SQL 2000 database.

The compatibility level is 80.

What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio Express. I would much prefer to use an automatic update script.

TIA

[Thomas Due]

Ok, a bit of an update. I have discovered that the error is due to a DEFAULT constraint on the columns in question. Problem is, this constraint has system generated name, so I can't know the name for certain.

As I said I would like to automate the update, but how can I automatically detect the DEFAULT constraints, remove them, alter the column and add the constraints again?

Preferable in T-SQL...

If I need to, I can make the update via. C# but I would much prefer to do it in a T-SQL script.

[Tibor Karaszi]

This is why you always should name your constraints. Here's an example on how to get the constraint
name using catalog views in 2005:

create table t(c1 int, c2 int default 1, c3 int default 3)

GO

SELECT df.name

FROM sys.default_constraints AS df

INNER JOIN sys.columns AS c

ON df.parent_object_id = c.object_id

AND df.parent_column_id = c.column_id

WHERE parent_object_id = object_id('t')

and c.name = 'c2'

Shouldn't be too hard to adapt above for 2000's system tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Thomas Due" <***@mail_remove_.dk> wrote in message news:%***@TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT constraint on the
columns in question. Problem is, this constraint has system generated name, so I can't know the name
for certain.

As I said I would like to automate the update, but how can I automatically detect the DEFAULT
constraints, remove them, alter the column and add the constraints again?

Preferable in T-SQL...

If I need to, I can make the update via. C# but I would much prefer to do it in a T-SQL script.

[Thomas Due]

Post by Tibor Karaszi
This is why you always should name your constraints.

Ay, I completely agree. I just didn't realize that DEFAULT also added a constraint reference.

The example you posted does not work with 2000. I guess the system tables follow another scheme. Inspired by it, I got this though:

create table t(c1 int, c2 int default 1, c3 int default 3)
GO

select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=d.depid and
d.depnumber=o.info
join syscolumns c on
d.id=c.id and
c.colid=o.info
where
o.parent_obj = object_id('t') and
c.name='c2'


It SEEMS to work. But does anyone have any comments on this, before I write a (probably) rather complex update script using this?

It only returns default constraints, but as that is what I needed, it suits my immediate needs.

[Thomas Due]

Thomas Due wrote:


Apparently I can manage with this:

create table t(c1 int, c2 int default 1, c3 int default 3)
go

select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=c.id and o.info=c.colid
where
o.parent_obj = object_id('t') and c.name='c2'

It still does not return other constraints than default constraints though.

[Uri Dimant]

Hi
Is it possible that some users (queries) are accessing the table and this
column?



"Thomas Due" <***@mail_remove_.dk> wrote in message news:***@TK2MSFTNGP02.phx.gbl...
Hi,

I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use

ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)

CoreWeight is originally defined as an INT.

My problem though, is that I can't.

Whenever I try this T-SQL command, I get this error:

Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.


I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.

The compatibility level is 80.

What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.

TIA