error in dropping a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

error in dropping a column

Author

Topic

jeanzz83
Starting Member

1 Post

Posted - 2004-04-04 : 05:15:04

i use this statemnet to add a column it will work but when i wanna drop that column it wont work
"ALTER TABLE LA_LeaveBalance ADD abc SMALLINT NOT NULL DEFAULT 3"

whereas if i use this statment
"ALTER TABLE LA_LeaveBalance ADD abc SMALLINT"

it works...
and if i use the previous sql statement it gives me an error

The object 'DF__LA_Balanc__accBa__540C7B00' is dependent on column 'accBalance'. ALTER TABLE DROP COLUMN accBalance failed because one or more objects access this column.

anyone knows wats the problem ? thanx. btw i'm using vb.net

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-04 : 10:03:46

You need to drop the default first. You have the name in the error message.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-04 : 14:36:03

To help you with this, look up DROP DEFAULT in Books Online. It has a couple examples you can cut and paste out of the bottom.

Start>All Programs>Microsoft SQL Server>Books Online

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-04 : 23:39:16

Here is a proc I wrote (you might want to sing it note for note....

) that will drop any constraints on a column, then the column :



/*
 * sp_DropCol
 * Pass in Table and Column name and it will drop any constraints on that column.
 * Then drop the column
 * Usage : Exec sp_DropCol 'Users', 'DepartmentID'
 */

Create Proc sp_DropCol

	@TableName nVarchar(200),
	@ColName nVarchar(200)

AS

Declare @CursSQL nvarchar(2000),
	@SQL nvarchar(2000),
	@ConstraintName nVarchar(300)

SELECT @CursSQL = 'SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ' +
	'WHERE TABLE_NAME = ''' + @TableName + ''' and ' +
	'COLUMN_NAME = ''' + @ColName + ''''

Create Table #constraints
(
	ConstraintName nVarchar(300)
)

INSERT INTO #constraints
Exec(@CursSQL)

Declare Cursor1 Cursor FOR
	SELECT * FROM #constraints

OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @ConstraintName
WHILE @@FETCH_STATUS = 0 BEGIN

	SELECT @SQL = 'Alter Table M_Member Drop Constraint ' + @ConstraintName
	Exec(@SQL)
	

	FETCH NEXT FROM Cursor1 INTO @ConstraintName
END
Close Cursor1
Deallocate Cursor1

DROP Table #constraints

SELECT @SQL = 'Alter Table M_Member Drop Column ' + @ColName
Exec(@SQL)


GO

Damian

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-04 : 23:42:00

Hey, that looks like a cursor.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-04 : 23:51:23

Lets shorten that a bit (haven't tried it so...)

Create Proc sp_DropCol
@TableName nVarchar(200),
@ColName nVarchar(200)

AS

Declare @SQL nvarchar(2000)

SELECT identity(int,1,1) as id, CONSTRAINT_NAME
into #a
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @TableName
and COLUMN_NAME = @ColName

declare @id int
select @id = 0
while @id < (select max(id) from #a)
begin
select @id = min(id) from #a where id > @id
select @sql = 'Alter Table ' + @TableName + ' Drop Constraint ' + CONSTRAINT_NAME
from #a where id = @id
exec (@sql)
end

SELECT @SQL = 'Alter ' + @TableName + ' Drop Column ' + @ColName
Exec(@SQL)
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-05 : 00:00:19

Fine then!

But you lose points for not including a Bobby McFerrin reference

Nice one Nigel, I hadn't thought about Information_Schema for that.

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-05 : 00:02:51

um - it's a direct copy from your post - I just got rid of the first dynamic sql query and used a temp table instead of a cursor.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-05 : 00:03:49

Yeah, all of our code is too old to use that. They didn't even have views back then.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-05 : 00:28:05

quote:
Originally posted by nr

um - it's a direct copy from your post - I just got rid of the first dynamic sql query and used a temp table instead of a cursor.

Oh yeah, it is too

OK, so I'm just an idiot today... I'll shut up now

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources