DROP on a culumn with constraint

Question

DROP on a culumn with constraint

Avron

SSC Veteran

Points: 294
More actions
October 11, 2004 at 5:14 am

#361192

The culumn from_core was created by a program that execute this code:
ALTER TABLE WebRsLog ADD [from_core][bit] NOT NULL DEFAULT 1
Now I need to drop this column. Using the code:
ALTER TABLE WebRsLog DROP COLUMN [from_core]
I get the error:
The object 'DF__WebRsLog__from_c__7C86175C' is dependent on column 'from_core'.
So, I need to drop the constraint first. A problem - the name of the constraint was created automaticly and is not known. Tried looking into the system tables without success. How can I find the constraint name or id when the only known data is the table name and the column name.
Avron Tal

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

You can get the constraint name FROM sysobjects WHERE xtype = 'C' AND WHERE parent_obj = the [id] FROM sysobjects WHERE [name] = 'WebRsLog'.

With this information you receive the different CONSTRAINT names.

The ABOVE is the HARD way.

The easy way is sp_help WebRsLog and look at the constraints and determine which needs to be removed.

Another way is to look at the message 'DF__WebRsLog__from_c__7C86175C'

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Avron SSC Veteran Points: 294 More actions · Answer 2

AJ Ahrens wrote:

You can get the constraint name FROM sysobjects WHERE xtype = 'C' AND WHERE parent_obj = the [id] FROM sysobjects WHERE [name] = 'WebRsLog'.

It is all has to be done by the software code. How will can you tell the right constraint (the one on this culomn) from all the rest (other columns defaults)

Avron

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This solution is one step better. But it doesn't take in consideration that a trigger / constraint check or foreign key may be needing this column

SELECT TOP 100 PERCENT Tables.name AS TableName, Const.name AS ConstraintName, dbo.syscolumns.name AS ColName FROM dbo.sysobjects Const INNER JOIN

dbo.sysobjects Tables ON Const.parent_obj = Tables.id INNER JOIN

dbo.syscolumns ON Const.id = dbo.syscolumns.cdefault

WHERE (Const.xtype = 'D') AND (Tables.id = object_id('YourTableName'))

ORDER BY Const.name

tushardighe Grasshopper Points: 23 More actions · Answer 4

You can use the code below. It will drop the column regardless of the name of the default constraint bound to it (similar code can be written to check for other types of constraints):

-- Standard Code Block to Drop a Column which may have a default constraint

-- (similar checks for other conatraints may be coded in too)

DECLARE @ConstraintName varchar(255)

DECLARE @TableName varchar(255)

DECLARE @ColumnName varchar(255)

--

SELECT @TableName='YourTableName'

SELECT @ColumnName='YourColumnName'

--

SELECT @ConstraintName = sysobjects.name

FROM sysObjects

INNER JOIN sysColumns ON sysObjects.id=sysColumns.cdefault

WHERE

objectProperty(sysObjects.id,'IsDefaultCnst')=1

AND sysColumns.name=@ColumnName

--Drop Constraint

IF @ConstraintName > ''

EXEC('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName)

-- Drop Column

EXEC('ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName)

GO

We're actually in the process of writing a bunch of SP's that will do what we want without complaining! Something like this (in pseudo-pseudo-code):

SP: Drop Column Dependencies

If exists column

If exists Constraint, Drop it (Default, Check, PK, UQ)

If exists Foreign Key(s), Drop it

If exists Index(s), Drop it

SP: Drop Column

If exists column

SP: Drop Column Dependencies

Drop Column

SP: Add Column

SP: Drop Column

Add Column

SP: Alter Column (Major)

If exists column

SP: Drop Column Dependencies

Alter Column

SP: Add Constraints

SP: Add FK(s)

SP: Add Index(s)

else

RAISE ERROR

SP: Alter Column (Minor)

If exists column

Alter Column

else

RAISE ERROR

SP: Alter to Identity (TODO: Later)

If exists column

??

else

RAISE ERROR

SP: Drop FK

If exists FK, Drop it

SP: Add FK

SP: Drop FK

Add FK

SP: Drop Constraint (Default, Check, PK, UQ)

If exists Constraint, Drop it

SP: Add Constraint (Default, Check, PK, UQ)

SP: Drop Constraint

Add Constraint

SP: Drop Table

If exists table, Drop Table ??

SP: Add Table

SP: Drop Table

Add Table

SP: Drop Index

If exists Index, Drop it

Add Index

SP: Add Index

SP: Drop Index

Add Index

-Tushar Dighe

Director, Technical Services

IST

Avron SSC Veteran Points: 294 More actions · Answer 5

Many thanks to tushardighe.

I did not understand the syscolumns table a doing more reading on it now.

Avron