Posted - 05/09/2005 : 14:24:23
My problem was that I need to alter some columns, but the alter statement throw an error: Cannot alter column ... its being replicated.
I was wondering about that error message bacause there are no replications on my database. I run sp_help on that table then realized that my column has a default value wich creates a default constraint.
Because the alter runs in an install kit, i had to have a script what drops the default constraint from that column.
I think the MSDN link is very useful, but the script doesn't work, so I made one based on that.
Here it is:
-- this script drops the default constraint from the given column in the given table
DECLARE @tablename VARCHAR(100),@columnname VARCHAR(100),@tab VARCHAR(100)
declare @defname varchar(100)
declare @cmd varchar(100)
select @defname = name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = @tablename
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id(@tablename) AND
name = @columnname)
select @cmd='alter table '+@tablename+ ' drop constraint '+@defname
if @cmd is null print 'No default constraint to drop'
Jason, thanks for your link, it helped me to write my own code.
Go on SQL Team!