title: How do I programatically verify, create, and update SQL table structure?
body: <p><strong>Scenario:</strong></p>
  
  <p>I have an application (C#) that expects a SQL database and login, which are set by a user. Once connected, it checks for the existence of several table and creates them if not found.</p>
  
  <p>I'd like to expand on this by having the program be capable of adding columns to those tables if I release a new version of the program which relies upon the new columns.</p>
  
  <p><strong>Question:</strong></p>
  
  <p>What is the best way to programatically check the structure of an existing SQL table and create or update it to match an expected structure?</p>
  
  <p>I am planning to iterate through the list of required columns and alter the existing table whenever it does not contain the new column. I can't help but wonder if there's an approach that is different or better.</p>
  
  <p><strong>Criteria:</strong></p>
  
  <p>Here are some of my expectations and self-imposed rules:</p>
  
  <ul>
  <li>Newer versions of the program might no longer use certain columns, but they would be retained for data logging purposes. In other words, no columns will be removed.</li>
  <li>Existing data in the table must be preserved, so the table cannot simply be dropped and recreated.</li>
  <li>In all cases, newly added columns would allow null data, so the population of old records is taken care of by having default null values.</li>
  </ul>
  
  <p><strong>Example:</strong></p>
  
  <p>Here is a sample table (because visual examples help!):</p>
  
  <pre>
  id  datetime         sensor_name  sensor_status  x1    x2    x3    x4
  1   20100513T151907  na019        OK             0.01  0.21  1.41  1.22
  2   20100513T152907  na019        OK             0.02  0.23  1.45  1.52
  </pre>
  
  <p>Then, in a new version, I may want to add the column <code>x5</code>. The "<code>x</code>-columns" are all data-storage columns that accept null.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I updated the sample table above. It is more of a log and not a parent table. So the sensors will repeatedly show up in this logging table with the values logged. A separate parent table contains the geographic and other logistical information <em>about</em> the sensor, making the table I wish to modify a child table.</p>