drop a column or record with default value constraint in sql server

If we want to drop particular field/column of the table then using Alter Table name of the table and Drop Column name of the column so we can do it using that syntax.

But if want to drop a field/column that has default constraint then it is very hard to do without its perfect syntax.

To drop default constraint column value you should familiar with default constraint name after then you can execute a simple Alter table name of table drop constraint name of the constraint.

Syntax:

Step 1

DECLARE @ConstraintName nvarchar(200)

SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = OBJECT_ID(N'__TableName__'))

IF @ConstraintName IS NOT NULL

EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('__TableName__') AND name='__ColumnName__')

EXEC('ALTER TABLE __TableName__ DROP COLUMN __ColumnName__')

Step 2

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__')

BEGIN

SELECT @ConstraintName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__'

EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

END

I will explain how to drop column with default constraint column value with my simple understanding methodology if you do not want to use above syntax.

Step 1: Get column id of your default constraints which you want to drop. Using following syntax we can get it. Name = N’_ column name _’ and OBJECT_ID = ‘_ Table Name _’ using object_id() built in function.

SELECT column_id FROM SYS.COLUMNS WHERE NAME = N'URL' AND OBJECT_ID = OBJECT_ID('[dbo].[TestExample11]');

Step 2: After getting column id give it to below Sys.Default_Constraints so you will get Name of Default Constraint of that column.

SELECT NAME FROM SYS.DEFAULT_CONSTRAINTS  WHERE PARENT_COLUMN_ID = 3 AND PARENT_OBJECT_ID = OBJECT_ID('[dbo].[TestExample11]');

Step 3: Use following syntax finally to remove default constraints.

ALTER TABLE [dbo].[TestExample11]

DROP CONSTRAINT DF__TestExample__URL__1273C1CD;

I hope you understand this well now. Also you can change this syntax like below.