Drop a column with a default constraint in SQL Server (IF EXISTS)

Question

I am writing a sql script for dropping column and a default constraint. The following script works fine but i like to know if it is a right way of doing it.

Can i drop a default constraint with a column in one statement instead of using two separate ones?

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')

BEGIN    
   ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID]    
END    
GO    
BEGIN   
  ALTER TABLE [dbo].[Employees] DROP COLUMN [EmpID]    
END

Answer 1

In SQL Server 2005 upwards you can drop both the constraint and the column in one statement.

The syntax is

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ]

The emphasis is on [ ,...n ], indicating multiple terms.

NB! Since the terms are processed sequentially, if the column being dropped is part of the constraint being dropped, then the constraint must be the first term, followed by the column term.

In your example:

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]

So your code would be:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employees_EmpID]') AND type = 'D')
BEGIN
   ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [DF_Employees_EmpID], COLUMN [EmpID]
END
GO

In SQL Server 2016 they have introduced the IF EXISTS clause which removes the need to check for the existence of the constraint first e.g.

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT IF EXISTS [DF_Employees_EmpID], COLUMN IF EXISTS [EmpID]