Delete Default coulm

Rohit Kulkarni 676 Reputation points
2022-09-07T12:16:34.24+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,662 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 9,206 Reputation points
    2022-09-07T12:45:22.62+00:00

    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];  
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. CosmogHong-MSFT 22,621 Reputation points Microsoft Vendor
    2022-09-08T03:14:11.847+00:00

    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.

    0 comments No comments