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

Question

I am writing a sql script for the undo column and the default constraint. The following script works fine, but I like to know if this is done correctly.

Is it possible to reset the default constraint using a column in one expression instead of 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 up, you can remove both a constraint and a column in a single expression.

Syntax

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

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

NB! Since the terms are processed sequentially, if the column to be deleted is part of the constraint to be dropped, then the constraint must be the first member followed by a member of the column.

In your example:

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

So your code will look like this:

 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 introduced the IF EXISTS clause, which eliminates the need to check for constraints first, for example.

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

Answer 2

Here is another way to remove columns and default constraints with a check to see if they exist before they are deleted:

 ------------------------------------------------------------------------- -- Drop COLUMN -- Name of Column: Column_EmployeeName -- Name of Table: table_Emplyee -------------------------------------------------------------------------- IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_Emplyee' AND COLUMN_NAME = 'Column_EmployeeName' ) BEGIN IF EXISTS ( SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]') AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]') ) BEGIN ------ DROP Contraint ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped' END -- ----- DROP Column ----------------------------------------------------------------- ALTER TABLE [dbo].table_Emplyee DROP COLUMN Column_EmployeeName PRINT 'Column Column_EmployeeName in images table was dropped' END -------------------------------------------------------------------------- -- ADD COLUMN Column_EmployeeName IN table_Emplyee table -------------------------------------------------------------------------- IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_Emplyee' AND COLUMN_NAME = 'Column_EmployeeName' ) BEGIN ----- ADD Column & Contraint ALTER TABLE dbo.table_Emplyee ADD Column_EmployeeName BIT NOT NULL CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] DEFAULT (0) PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added' PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added' END GO 

Answer 3

How good you are.

An alternative would be

 IF OBJECT_ID('DF_Employees_EmpID', 'D') IS NULL BEGIN ALTER TABLE dbo.Employees DROP COLUMN EmpID END ELSE BEGIN ALTER TABLE dbo.Employees DROP CONSTRAINT DF_Employees_EmpID, COLUMN EmpID END 

If there is a limitation, this combines the two operations into a single statement / transaction.

Answer 4

Another solution:

 DECLARE @TableName sysname, @Schema sysname, @colname sysname, @sql VARCHAR(1000) SELECT @Schema = 'dbo', @TableName = 'mytable', @colname = 'mycol' IF COL_LENGTH(@Schema+'.' +@TableName , @colname) IS NULL BEGIN PRINT 'Column does not exist!' END ELSE BEGIN SET @sql = '' SELECT @sql += N' ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + default_constraints.name + ';' FROM sys.all_columns INNER JOIN sys.tables ON all_columns.object_id = TABLES.object_id INNER JOIN sys.schemas ON TABLES.schema_id = schemas.schema_id INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id WHERE schemas.name = @Schema AND tables.name = @TableName AND all_columns.name = @colname SET @sql += N' ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @colname + ';' PRINT ISNULL(@sql, 'NULL') EXECUTE(@sql) END