title: MS SQL update same column in multiple tables using a cursor
body: <p>I have a database containing a lot of tables, most of which have a column called <code>contract</code> which is a varchar(255) and is used as a foreign key.  The following query selects all table names that contain this column (33 in total):</p>
  
  <pre><code>SELECT name FROM sys.objects WHERE object_id IN (
      SELECT object_id FROM sys.columns WHERE Name = N'contract'
  ) AND name &lt;&gt; N'sysdercv'
  </code></pre>
  
  <p>Now, I need to change all of the contract names (not many) into anonymous identifiers so I can use screenshots without revealing sensitive data, but I need to maintain the relationships so the application still works.  Essentially, I want to change all instances of contract='SomeCompany' to contract='example1' in all of the tables returned by the above query.</p>
  
  <p>My initial attempt to do so is as follows:</p>
  
  <pre><code>declare @tablename sysname
  
  declare C cursor fast_forward for
  SELECT name FROM sys.objects WHERE object_id IN (
      SELECT object_id FROM sys.columns WHERE Name = N'contract'
  ) AND name &lt;&gt; N'sysdercv'
  
  open C
  fetch next from C into @tablename
  
  while @@fetch_status = 0
  begin
      update @tablename set contract='example1' where contract='SomeCompany'
      fetch next from C into @tablename
  end
  
  close C
  deallocate C
  go
  </code></pre>
  
  <p>However, this gives the error <code>Must declare the table variable "@tablename".</code></p>
  
  <p>So my questions are:</p>
  
  <ol>
  <li>Is what I am trying to do possible, or do I need to manually go through and update all the tables myself?</li>
  <li>If so, am I on the right lines or am I barking up entirely the wrong proverbial tree?</li>
  </ol>