Search code examples
sqlsql-serverheidisql

How make a TRY CATCH drop

I want get this object name

enter image description here

to make a drop of a constraint with that name

ALTER TABLE dbo.Establecimientos 
    DROP CONSTRAINT DF__Estableci__proce__498EEC8D

where DF__Estableci__proce__498EEC8D is the name of the object with whom I have conflict

I need catch that error and replace my code for him automatically.

Solution

  • What you could do is check if there's a default constraint before dropping the column.

    This query will return you the name of the default constraint that exists on the column you want to drop. Just replace the table name and column name for the one you want to drop.

    select dc.name 
from sys.default_constraints dc
join sys.tables t on t.object_id=dc.parent_object_id
join sys.columns c on c.object_id=t.object_id and c.column_id=dc.parent_column_id
where t.name='YourTABLE'
and c.name='YourCOLUMN'

    With this query, you will create the "DROP CONSTRAINT" and execute it for your table and column. Remember to do this before dropping the column.

    DECLARE @Query nvarchar(max)

select @Query='ALTER TABLE '+ schema_name(t.schema_id) + '.' + t.name + ' DROP CONSTRAINT ' + dc.name 
from sys.default_constraints dc
join sys.tables t on t.object_id=dc.parent_object_id
join sys.columns c on c.object_id=t.object_id and c.column_id=dc.parent_column_id
where t.name='YourTABLE'
and c.name='YourCOLUMN'

exec sp_execute @Query