Delete Default coulm

Question

Hello Team,

I have created a default in a table in SQL Server

example

/****** Object: Table [S4].[VBEP_TEST] Script Date: 9/7/2022 5:25:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [S4].VBEP_TEST ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [S4].[VBEP_TEST] ADD DEFAULT (CONVERT([date],getdate())) FOR [CurrentDate]
GO

ALTER TABLE [S4].[VBEP_TEST] ADD DEFAULT (CONVERT([time],dateadd(hour,(-7),getdate()))) FOR [CurrentTime]
GO

Now i am trying to delete the default column with DROP syntax .it is now working.

Example:
Alter table Name
drop column name

Please advise

Answer 1

You need to drop the dependent default constraint before you drop the column. Since you did not specify a name for the default constraint when it was created, SQL Server generated a name for you. You'll need to find the constraint name, drop the constraint, and finally drop the column. For example:

--get default constraint name for CurrentDate column  
SELECT df.name AS DefaultConstraintName  
FROM sys.default_constraints AS df  
JOIN sys.columns AS c ON c.object_id = df.parent_object_id AND c.column_id = df.parent_column_id  
WHERE   
	df.parent_object_id = OBJECT_ID(N'S4.VBEP_TEST')  
	AND c.name = N'CurrentDate';  
GO  
  
--specify constraint name from above query to drop the constraint  
ALTER TABLE S4.VBEP_TEST  
	DROP CONSTRAINT DF__VBEP_TEST__Curre__267ABA7A;  
GO  
  
--drop the column  
ALTER TABLE S4.VBEP_TEST  
	DROP COLUMN CurrentDate;  
GO  

Consider providing explicit constraint names to avoid the need to lookup the constraint name. This will make subsequent DDL changes easier:

ALTER TABLE [S4].[VBEP_TEST]   
	ADD CONSTRAINT DF_VBEP_TEST_CurrentDate  
	DEFAULT (CONVERT([date],getdate())) FOR [CurrentDate];  

Answer 2

Hi @Rohit Kulkarni
Check this code:

DECLARE @TableName sysname,  
        @Schema sysname,  
        @colname sysname,  
        @sql VARCHAR(1000)  
  
SELECT @Schema = '[S4]',  
       @TableName = '[VBEP_TEST]',  
       @colname = '[CurrentTime]'  
  
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 + ';'  
  END  
  --PRINT @sql  
EXEC(@sql)  

Find more details in this similar thread: Drop a column with a default constraint in SQL Server (IF EXISTS)

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.