Drop column with default constraint

Hello
I have a table with column which have default constraint
(SQL server generated name of constraint).
Is there a way to drop this column without re-creating the table?
Example:
create table abc (xxx int, yyy int default 0);
alter table abc drop column yyy;
Thanks
TibXSee thread:
SQL Help with Drop Column
http://tinyurl.com/65nva
Bryce|||It's a good idea to give a default a meaningful name when you create
it. Query Analyzer will show you the name of the constraint in the
object browser (the name of a default begins with DF and includes the
table and column name). Then you can drop it in the usual way:
ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
ALTER TABLE abc DROP COLUMN yyy;
David Portas
SQL Server MVP
--|||Try,
use northwind
go
create table t (
colA int,
colB varchar(25) default ('aaaaa')
)
go
declare @.sql nvarchar(4000)
declare @.cnstname sysname
select
@.cnstname = [name]
from
sysobjects as so
where
xtype = 'D'
and parent_obj = object_id('dbo.t')
and col_name(parent_obj, info) = 'colB'
if @.cnstname is not null
begin
set @.sql = N'alter table dbo.t drop constraint ' + @.cnstname
execute sp_executesql @.sql
end
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
alter table dbo.t
add constraint df_t_colB default ('aaaaa') for colB with values
go
alter table dbo.t
drop constraint df_t_colB
go
alter table dbo.t
drop column colB
go
alter table dbo.t
add colB varchar(25)
go
create default df_t_colB as 'aaaaa'
go
execute sp_bindefault df_t_colB, 't.colB'
go
execute sp_unbindefault 't.colB'
go
drop default df_t_colB
go
drop table dbo.t
go
AMB
"TibX" wrote:

> Hello
> I have a table with column which have default constraint
> (SQL server generated name of constraint).
> Is there a way to drop this column without re-creating the table?
>
> Example:
> create table abc (xxx int, yyy int default 0);
> alter table abc drop column yyy;
>
> Thanks
> TibX
>|||The problem is that I have several installations
with generated name of that default constraint
and I need drop column by a script.
TibX
David Portas wrote:
> It's a good idea to give a default a meaningful name when you create
> it. Query Analyzer will show you the name of the constraint in the
> object browser (the name of a default begins with DF and includes the
> table and column name). Then you can drop it in the usual way:
> ALTER TABLE abc DROP CONSTRAINT DF__abc__yyy__208E6DA8;
> ALTER TABLE abc DROP COLUMN yyy;
>|||Try this:
DECLARE @.df SYSNAME
SET @.df =
(SELECT OBJECT_NAME(cdefault)
FROM SYSCOLUMNS
WHERE id = OBJECT_ID('dbo.abc')
AND name = 'yyy')
IF @.df IS NOT NULL
BEGIN
EXEC sp_rename @.df, 'df_to_drop', 'OBJECT'
ALTER TABLE dbo.abc DROP CONSTRAINT df_to_drop
END
ALTER TABLE dbo.abc DROP COLUMN yyy
David Portas
SQL Server MVP
--|||Thanks for your solutions.
I use
select name from sysobjects ...
TibX