title: Reset Identity Seed ('DBCC CHECKIDENT ... RESEED' is not working)
body: <p>For my unit tests I have an automated process which builds a copy of a DB, table by table. I first of all run a <code>SELECT INTO</code> and add the primary key and any indices.</p>
  
  <p>Some of the tables I copy with data, and for those <em>without</em> data I wish to reset the identity seed.</p>
  
  <p>To achieve this I have the following <code>T-SQL</code> statement:</p>
  
  <pre><code>EXEC('TRUNCATE TABLE [' + @TableName + '];')
  IF EXISTS (SELECT * FROM sys.identity_columns WHERE object_id = OBJECT_ID(@TableName) AND last_value IS NOT NULL)
  BEGIN
      EXEC('DBCC CHECKIDENT (' + @TableName + ', reseed, 1)')
  END
  </code></pre>
  
  <p>The query runs without any reported problems but when I inspect the table in design view the <em>Identity Seed</em> value is never reset.</p>
  
  <p>Can anyone please tell me why the Identity Seed is not being reset, or what else I need to do?</p>
  
  <p><strong>UPDATE:</strong>
  As per some comments I have modified the <em>sys.identity_columns</em> to the following:</p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.identity_columns WHERE object_id = OBJECT_ID(@TableName))
  </code></pre>
  
  <p>When run, this returns the following message:</p>
  
  <blockquote>
    <p>Checking identity information: current identity value '1'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.</p>
  </blockquote>
  
  <p>Which looks promising. However, if I then refresh the table (in the Object Explorer) and look at it in the design view, the Identity Seed is unchanged, <em>i.e.</em> not 1 (or 0) but 135.</p>