ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column.

Question

ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column.

whiz

SSC-Addicted

Points: 429
More actions
March 7, 2008 at 2:36 pm

#203936

Hi I’m trying to alter a table and delete a column
I get the following error.
The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column.
I tried deleting the concerned constraint.
But the next time I get the same error with a different constraint name.
I want to find out if I can check the constraint name dynamically and delete it and then drop the column.
Can anyone help.
IF EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = Tablename
AND syscolumns.name = column name)
BEGIN
EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')
EXECUTE ('ALTER TABLE tablename
Drop column columnname)
END
GO

Viewing 10 posts - 1 through 9 (of 9 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Delete the column in SSMS. You can then generate the change to script for review. SSMS will include all the drops for all the constaints, keys etc..

To generate the script just right click in the dialog window and choose generate script.

My blog: http://jahaines.blogspot.com

whiz SSC-Addicted Points: 429 More actions · Answer 2

the code generated in the SSMS window is same as i wrote.

my problem is as follows.

1. i write a script to make schema changes in a DB

2. i have to provide a rollback script that will remove those changes

in my rollback script, i dont know what the constraint name is.

i need some script where i can say

@constraint name = find default constraint for this column or table

drop constraint @constraint name

drop column.

thanks for the help though.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

1. i write a script to make schema changes in a DB
2. i have to provide a rollback script that will remove those changes

Your rollback should undo everything your schema change script did. So if you added columns and constraints in your implementation script, can't you get the names of all the objects you created, dropped and altered? This should include constraints. Once you have the names, you can reverse the operation.

Am I missing something?

My blog: http://jahaines.blogspot.com

whiz SSC-Addicted Points: 429 More actions · Answer 4

my implementation script is as follows

IF NOT EXISTS(SELECT 1

FROM sysobjects,

syscolumns

WHERE sysobjects.id = syscolumns.id

AND sysobjects.name = 'Morningstarcategory'

AND syscolumns.name = 'LastUpdated')

BEGIN

EXECUTE ('ALTER TABLE Morningstarcategory

ADD LastUpdated DateTime null

DEFAULT (getdate())')

END

i specify that the default date value in the column is getdate()

but the system assigns it a name like "DF__SecurityM__DsegL__08C105B8"

that's why i cant find the name of it.

thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

whiz.laksh (3/7/2008)
i specify that the default date value in the column is getdate()
but the system assigns it a name like "DF__SecurityM__DsegL__08C105B8"
that's why i cant find the name of it.

Try something like this:

IF EXISTS(SELECT 1

FROM sysobjects,

syscolumns

WHERE sysobjects.id = syscolumns.id

AND sysobjects.name = 'Morningstarcategory'

AND syscolumns.name = 'LastUpdated')

BEGIN

DECLARE @df nvarchar(255)

SELECT @df = D.name

FROM sys.default_constraints D,

sysobjects,

syscolumns

WHERE sysobjects.id = syscolumns.id

AND sysobjects.name = 'Morningstarcategory'

AND syscolumns.name = 'LastUpdated'

AND D.parent_object_id = sysobjects.id

AND D.parent_column_id = syscolumns.id

IF NOT @df IS NULL

BEGIN

DECLARE @sql nvarchar(max)

Select @sql = 'ALTER TABLE dbo.DO_Revenue

DROP CONSTRAINT ' + @df

EXECUTE (@sql)

END

EXECUTE ('ALTER TABLE Morningstarcategory

ADD LastUpdated DateTime null

DEFAULT (getdate())')

END

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

You can name the constraints. You dont have to let them auto generate.

ALTER TABLE Morningstarcategory

ADD LastUpdated DateTime null

CONSTRAINT Morningstarcategory_LastUpdated_DF DEFAULT GETDATE()

My blog: http://jahaines.blogspot.com

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Adam Haines (3/7/2008)
You can name the constraints. You dont have to let them auto generate.

Good point. I had forgotten that.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

whiz SSC-Addicted Points: 429 More actions · Answer 8

Thanks Adam and Barry for your answers.

i tried both your approaches.

Adam's method worked for me.

but one more point here is, when i tried using "sys.default_constraints" i got an error message in SSMS saying "sys.default_constraints is not a valid object"

any idea why i get that message?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Got me. "sys.default_constraints" is a standard Catalog View in SQL Server 2005.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]