Help with ALTER COLUMN needed

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
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
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
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.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
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
|||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" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.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
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Tibor Karaszi wrote:

>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=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'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
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||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=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =
though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
sql