Simon A. Eugster Simon A. Eugster - 5 months ago 31
SQL Question

How do I drop a column with object dependencies in SQL Server 2008?

The error message I'm obtaining when trying to drop a column:


The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.

Msg 5074, Level 16, State 1, Line 43

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


I have already tried to find the default constraints, as described here:
http://stackoverflow.com/questions/314998/sql-server-2005-drop-column-with-constraints

Unfortunately without any success :( The line returned is:

fkKeywordRolleKontakt 2 814625945 0 defEmptyString


And I cannot remove either of
fkKeywordRolleKontakt
and
defEmptyString
.

What is the correct way to get rid of this dependency?

EDIT: Perhaps this is of importance too. The column fkKeywordRolleKontakt is of type udKeyword (nvarchar(50)) with default
dbo.defEmptyString
.


Edit 2: Solved

I could solve the problem now. I'm sorry, I did not copy the full error message, which was:

Msg 5074, Level 16, State 1, Line 1

The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.

Msg 5074, Level 16, State 1, Line 1

The object 'FK_tlkpRolleKontakt_tlkpKeyword' is dependent on column 'fkKeywordRolleKontakt'.

Msg 4922, Level 16, State 9, Line 1

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


I could generate a script to drop the column by right-clicking on the column entry (dbo.tlkpRolleKontakt > Columns > fkKeywordRolleKontakt) (in MSSQL Server Manager), selecting Modify and deleting the column. Then Table Designer > Generate Change Script generated the necessary commands:

ALTER TABLE dbo.tlkpRolleKontakt
DROP CONSTRAINT FK_tlkpRolleKontakt_tlkpKeyword
EXECUTE sp_unbindefault N'dbo.tlkpRolleKontakt.fkKeywordRolleKontakt'
ALTER TABLE dbo.tlkpRolleKontakt
DROP COLUMN fkKeywordRolleKontakt


That's it :)

Answer

I could solve the problem now. I'm sorry, I did not copy the full error message, which was:

Msg 5074, Level 16, State 1, Line 1
The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK_tlkpRolleKontakt_tlkpKeyword' is dependent on column 'fkKeywordRolleKontakt'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or more objects access this column.

I could generate a script to drop the column by right-clicking on the column entry (dbo.tlkpRolleKontakt > Columns > fkKeywordRolleKontakt) (in MSSQL Server Manager), selecting Modify and deleting the column. Then Table Designer > Generate Change Script generated the necessary commands:

ALTER TABLE dbo.tlkpRolleKontakt
    DROP CONSTRAINT FK_tlkpRolleKontakt_tlkpKeyword
EXECUTE sp_unbindefault N'dbo.tlkpRolleKontakt.fkKeywordRolleKontakt'
ALTER TABLE dbo.tlkpRolleKontakt
    DROP COLUMN fkKeywordRolleKontakt
Comments