title: SQL Server 2008 R2 - DROP COLUMN, INFORMATION_SCHEMA.COLUMNS and IF EXISTS
body: <p>I have a column that is functionally a duplicate of another column. I want to copy the value of the surplus column to the other and then drop the extraneous column.  </p>
  
  <p>The problem is the script is conditional amid other changes to the database - I like to be able to restore the database to the exact state it was in via an "undo" script. This script and its corresponding "do" script are written conditionally so that they can be run repeatedly without error.  </p>
  
  <p>This particular block when executed a second time, fails with invalid column. It seems to think the column is still there even though both <code>INFORMATION_SCHEMA.COLUMNS</code> and <code>sys.columns</code> report no column exists.  </p>
  
  <pre><code>if exists (select * from sys.objects where name = 'flint') 
      drop table flint
  
  create table flint ( fred int, barny int )
  go
  
  select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'barny' and TABLE_NAME = 'flint'
  
  if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'barny' and TABLE_NAME = 'flint')
  begin
      update flint set fred = barny
      alter table flint drop column barny
  end
  go
  
  select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'barny' and TABLE_NAME = 'flint'
  
  if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'barny' and TABLE_NAME = 'flint')
  begin
      update flint set fred = barny
      alter table flint drop column barny
  end
  go
  </code></pre>
  
  <p>Why does the second block get executed and fail with 'invalid column barny'?</p>