Doubt with DROP COLUMN sql server command

Question

I tried to delete a column from a database, checked that there are restrictions.

ALTER TABLE dbo.TB_ESTRACAO DROP COLUMN MULTIPLIER

I get a message, how can I resolve this? I thank you!

Message 5074, Level 16, Status 1, Line 1 The Object 'DF_TB_ESTRACAO_MULTIPLICADOR' is dependent on column 'MULTIPLIER'. Message 4922, Level 16, Status 9, Line 1 ALTER TABLE DROP COLUMN MULTIPLIER failed because one or more Objects access this column.

Answer 1

The error message says that the object DF_TB_ESTRACAO_MULTIPLICADOR refers to the column dbo.TB_ESTRACAO.MULTIPLICADOR and so you can not exclude it.

The following queries can help understand who depends on whom:

Searching Objects that depend on the column:

SELECT
    OBJECT_NAME(OBJECT_ID),definition
FROM
    sys.sql_modules
WHERE
    definition LIKE '%MULTIPLICADOR%'

Searching Stored Procedures that depend on the column:

SELECT
    DISTINCT OBJECT_NAME(OBJECT_ID),
    object_definition(OBJECT_ID)
FROM
    sys.Procedures
WHERE
    object_definition(OBJECT_ID) LIKE '%MULTIPLICADOR%'

I hope I’ve helped!

Answer 2

The message indicates that a constraint (Constraint) has been created with default value for the column we are trying to delete. Before excluding this column, the restriction should be excluded.

ALTER TABLE TB_ESTRACAO DROP CONSTRAINT DF_TB_ESTRACAO_MULTIPLICADOR

Then we can delete the column:

ALTER TABLE TB_ESTRACAO DROP COLUMN MULTIPLIER