Remove default value on column in SQL table before setting a new one

Posted on 2007-09-29
Last Modified: 2012-06-27
Hello Experts,

I am using dynamic SQL in the following SPROC to set the default value in a tables column. This works very nicely as long as there has not already been a defult value set.

This is the Error message coming from SQL Server when I run this SPROC on a table that already has a default value in the clomumn:

"Column already has a DEFAULT bound to it.\r\nCould not create constraint. See previous errors."}      System.Exception {System.Data.SqlClient.SqlException}

I can do this by creating and deleting temporary tables (Add the new default to a temp table, insert data into the temp table, copy date from temp table to main, drop and create temp  table etc) but this is cumbersome.

 Is there a way to delete the Default value first before running the SPROC to add a new one??



 @TripNum varchar(10),
 @TableName nvarchar(64),

--Dynamic SQL
      SET @TripNum = '2007B'
      SET @TableName  = 'dbo.ObsData'
      SET @SQL = 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD DEFAULT ''' +  @TripNum + ''' FOR TripNum'


Question by:Saxitalis
    LVL 142

    Accepted Solution

    see here how to identify and drop an existing default on a column:

    Author Comment

    Got it - Much cleaner than how I was going about it...


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how the fundamental information of how to create a table.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now