title: SQL Server how to drop identity from a column
body: <p>Is there an easy way to remove an identity from a table in SQL Server 2005?  </p>
  
  <p>When I use Management Studio, it generates a script that creates a mirror table without the identity, copies the data, drops the table, then renames the mirror table, etc.  This script has 5231 lines in it because this table/column have many FK relations. </p>
  
  <p>I'd feel much more comfortable running a simple alter/drop.  Any ideas?</p>
  
  <p><strong>EDIT</strong><br>
  I think I'm just going to go with the 5,231 line script from Enterprise Manager.  However, I'm going to break it up into smaller parts which I can run and control better.  This table "behaves" strange, if you try to delete 1 row (even one you just inserted, which is not in any other FK table), you get this error:</p>
  
  <pre><code>delete MyTable where MyPrimaryKey=1234  
  
  Msg 8621, Level 17, State 2, Line 1
      The query processor ran out of stack space during query optimization. Please simplify the query.
  </code></pre>
  
  <p>No doubt, all the FKs.  We will halt all access to our application and run in single user mode when we make these schema and related application changes.  However, we need this to run fast, and I need an idea of how long it will take.  I guess that I'll just have to test, test, test.</p>