I cannot change column type -> Object Depends on the column in question

Asked

Viewed 2,363 times

1

I need to modify the column type of a client table, however this change will be made through a program written in VB.Net and therefore needs to be done via SQL command.

The point is that when using the command: 

ALTER TABLE Customer ALTER COLUMN COLUNA int;

I receive the following message:

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

I cannot in any way apply any amendment to the column in question...

sql-server
  • 1

    Is this SQL Server? It seems to me that you have a reference to this column somewhere, try to find where it is.

  • Exactly, using sql server 2008 R2

  • 1

    May have been created some Constraint automatically by DB. Can be in stored Procedure, in Function. Have you tried with NOCHECK CONSTRAINT? Remember to experiment on data that can be corrupted if you do something wrong. you may have to make a DROP CONSTRAINT.

  • Funny, that when I try to remove the Constraint 'Df__tmp_customer_coluna__15bb0e23' sql tells me that this object does not exist... but when I try to change the column, sql tells me that this Constraint is dependent on the field.

  • 1

    Probably because DB created it. It might even have been created because of default value. Have you tried going through all the objects in the database looking for it? Can you do darlings in the DB system tables (not in their tables)?

  • Guys, I managed to solve... I managed a Constraint drop script by the bank and removed it quietly.

  • 1

    @Kleitonribeiro add the script as a response or try to give some lights as resolved so that future generations also know how to solve.

  • @Omni ready :D entered the answer !

  • @Please insert your solution as an answer, not as an edit to your question.

  • @Omni All right, thank you very much!

Show 5 more comments

1 answer

1


Troubleshooting :

Script:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[NomeDaConstraint]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[NomeDaTabela] DROP CONSTRAINT [NomeDaConstraint]
END

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.