title: Changing varchar to nvarchar for all table columns in a database
body: <p>Is there a way to fully traverse all tables in the database and changing all the <code>varchar</code> datatype to <code>nvarchar</code> datatype?</p>
  
  <p>The simple SQL below does not work as intended due to constraints from indexing, unique keys and what not.</p>
  
  <pre><code>ALTER TABLE [CUS].[Customers] 
      ALTER COLUMN [CustomerNo] NVARCHAR(500);
  </code></pre>
  
  <p>I tried changing them from SSMS via the GUI way and the profiler actually shows what it does to truly change the datatype.</p>
  
  <ul>
  <li>Step 1: it creates a temporary table, with <code>nvarchar</code> column changed from <code>varchar</code></li>
  <li>Step 2: set some lock escalation (no idea what this is)</li>
  <li>Step 3: inserts all data from original table into the temporary table</li>
  <li>Step 4: drops original table</li>
  <li>Step 5: renames temporary table to original table's name</li>
  <li>Step 6: adds the constraints</li>
  <li>Step 7: adds the indexes</li>
  </ul>
  
  <p>This involves a lot of steps and it is quite cumbersome if I were to do it manually.</p>
  
  <p>Can anyone suggest a more flexible solution to my problem?</p>