title: Drop default constraint on a column in TSQL
body: <p>I have a table with a column like this that is currently live:</p>
  
  <pre class="lang-sql prettyprint-override"><code>name NVARCHAR(128) NOT NULL DEFAULT ''
  </code></pre>
  
  <p>I am altering the column like this to make it nullable:</p>
  
  <pre class="lang-sql prettyprint-override"><code>ALTER TABLE  mytable ALTER COLUMN name NVARCHAR(128) NULL
  </code></pre>
  
  <p>However, the default constraint, named 'DF__mytable__datab__7DE4B36' in one instance of the table, still remains. I know this could have been avoided if the original author named the constraint. I have several of instances of these tables but I don't want to manually delete every constraint in every table I have. What is the easiest and most elegant way of dropping this default constraint on a column in Sql Server that I can uniformily apply to every instance of this table?</p>
  
  <h2>EDIT</h2>
  
  <p>This is the script that I ended up using:</p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @table_id AS INT
  DECLARE @name_column_id AS INT
  DECLARE @sql nvarchar(255) 
  
  -- Find table id
  SET @table_id = OBJECT_ID('mytable')
  
  -- Find name column id
  SELECT @name_column_id = column_id
  FROM sys.columns
  WHERE object_id = @table_id
  AND name = 'name'
  
  -- Remove default constraint from name column
  SELECT @sql = 'ALTER TABLE mytable DROP CONSTRAINT ' + D.name
  FROM sys.default_constraints AS D
  WHERE D.parent_object_id = @table_id
  AND D.parent_column_id = @name_column_id
  EXECUTE sp_executesql @sql
  </code></pre>
  
  <p>Another script that can be used to accomplish this can be found here:
  <a href="https://stackoverflow.com/questions/1430456/how-to-drop-sql-default-constraint-without-knowing-its-name">How to drop SQL default constraint without knowing its name?</a></p>
  
  <p>Thanks!</p>