Problem - deleting columns with default value

[Artur Orlowski]

I have a problem with aplication which is deleting columns from table.

First I was using commands:
Adding: ALTER TABLE table_name ADD column_name int
Deleting: ALTER TABLE table_name DROP COLUMN column name
It worked perfectly.

Then I needed default value in new column.
Adding was easy:
ALTER TABLE table_name ADD column_name int DEFAULT 0
But when I tried to delete this column (as above) I received error:
(for column_name: L17 table_name: LIMITY_WARTOSCI):

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__LIMITY_WART__L17__53A266AC' is dependent on column L17'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN L17 failed because one or more objects access
this column.

Then I tried to delete default value from this column before deleting
column.
But command from MSDN examples:
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT
generated syntax error (!!!) (Incorrect syntax near the keyword 'DEFAULT'.)

Thanks in advance for any advice how to delete this type columns.

Artur Orlowski

[John Bell]

Hi

You must be reading the syntax wrong for the drop command You need to drop
the default constraint:

ALTER TABLE MyTable DROP CONSTRAINT MyColumnDefaultContraint

then drop the column

ALTER TABLE MyTable DROP COLUMN MyColumn.

To get the constraints on a given table try

sp_helpconstraint 'MyTable'

John

"Artur Orlowski" <art...@edu.com.pl> wrote in message
news:3D67BE2A...@edu.com.pl...

[BP Margolin]

Artur,

Just to add a bit to the information posted by John ...

It's usually a good policy to explicitly name constraints. One of the
advantages is by explicitly naming them, you can more easily use the ALTER
TABLE command.

So, for example, if you had initially done:

create table Artur (c1 int NOT NULL constraint df_c1 default (5))

you could later do:

alter table Artur drop constraint df_c1

In the meantime, as indicated by John, you can use either the
sp_helpconstraint or sp_help system stored procedures to find out the system
generated name of the default constraint.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Artur Orlowski" <art...@edu.com.pl> wrote in message
news:3D67BE2A...@edu.com.pl...

[BP Margolin]

Artur,

Just to add a bit to the information posted by John ...

It's usually a good policy to explicitly name constraints. One of the
advantages is by explicitly naming them, you can more easily use the ALTER
TABLE command.

So, for example, if you had initially done:

create table Artur (c1 int NOT NULL constraint df_c1 default (5))

you could later do:

alter table Artur drop constraint df_c1

In the meantime, as indicated by John, you can use either the
sp_helpconstraint or sp_help system stored procedures to find out the system
generated name of the default constraint.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Artur Orlowski" <art...@edu.com.pl> wrote in message
news:3D67BE2A...@edu.com.pl...

[Steve Kass]

Artur,

Here's an example of how you can drop a column on which you've
put a default constraint without specifying a name for that constraint,
... assuming it's the only default on that table. If there is more than one,
you might drop them all and add back the ones you still need, this time
with names.

CREATE TABLE MyTable (
i int
)

ALTER TABLE MyTable ADD MyColumn varchar(10) DEFAULT '' NOT NULL
GO

DECLARE @DefaultName sysname
SET @DefaultName = (
SELECT name
FROM sysobjects
WHERE name LIKE 'DF__MyTable%'
)

DECLARE @sql varchar(200)
SET @sql = 'ALTER TABLE MyTable DROP CONSTRAINT ' + @DefaultName
EXEC(@sql)

ALTER TABLE MyTable DROP COLUMN MyColumn

GO

DROP TABLE MyTable

Steve Kass
Drew University

[oj]

steve,

it's safer to do this if dfname was specified during its creation:

DECLARE @DefaultName sysname
SET @DefaultName = (

select top 1 o2.name
from sysobjects o1, sysobjects o2
where o2.parent_obj=o1.id
and o2.xtype='d'
and o1.name='mytable'
)

--
-oj
Rac v2.1 Public Beta is RELEASED.
http://www.rac4sql.net

"Steve Kass" <sk...@drew.edu> wrote in message
news:3D68085C...@drew.edu...

[Steve Kass]

True, I could be dropping something else, I guess, like a default
for myTableNOT... Your suggestion is safer, and here's a tiny
improvement, I think:

DECLARE @DefaultName sysname
SET @DefaultName = (

select top 1 name
from sysobjects
where parent_obj=object_id('myTable')
and xtype='d'
)

SK

[oj]

yeah...for a single table there is no need for self join...:~)

--
-oj

"Steve Kass" <sk...@drew.edu> wrote in message

news:3D683C43...@drew.edu...

[Steve Kass]

Of course, object_id('myTable') probably selects
from sysobjects anyway, in which case it's no faster.

-SK

[Markus Ewert]

Steve Kass <sk...@drew.edu> wrote in message news:<3D684CE7...@drew.edu>...

Hello Steve,

there's one problem with your statement. You cannot be sure, that the
constraint name you get is really the default constraint on the column
you want.
In fact, you get the first default constraint which can be on any
column in the table. Here is an improved statement with a join on the
columns:

select o2.name
from sysobjects o1, syscolumns c1, sysconstraints c2, sysobjects o2
where o1.name='MyTable' and c1.name='MyColumn' and o1.id=c1.id
and o1.id=c2.id and c2.status & 0xf = 5 and c1.colid = c2.colid
and c2.constid=o2.id

You can also use the stored procedure sp_unbindefault
'MyTable.MyColumn' to drop the default constraint.

Markus