title: SQL Azure: How to execute a common DDL script across all schemas?
body: <p>Is there a built-in way to execute a common DDL script across all schemas?</p>
  
  <p>I'm working on a multi-tenant application which creates a database schema for each tenant. Each schema contains the same table definitions for each tenant. For example:</p>
  
  <pre><code>Schema named "tenant1" contains tables: tenant1.Users, tenant1.HistoryRecords, etc.
  Schema named "tenant2" contains tables: tenant2.Users, tenant2.HistoryRecords, etc.
  </code></pre>
  
  <p>When I add a field I want it to be added in tenant1 schema, tenant2 schema, etc.</p>
  
  <p><strong>Initial thoughts:</strong>
  I have a table which contains the schema names and related information for the tenant. I'm thinking of adding a database version field to this table to keep track of schema changes. I would then create a stored procedure which accepts the DDL script and schema version as parameters. </p>
  
  <pre><code>CREATE PROCEDURE UpdateSchema(DDLScript, InitialSchemaName, DbVersion)
      @DDLScript nvarchar(5000), 
      @InitialSchemaName nvarchar(10), 
      @DbVersion nvarchar(5)...
  </code></pre>
  
  <p>The script would loop through the set of schemas, running the DDL Script for each one, replacing the InitialSchemaName with the schema name for the current loop, and commit the changes for all if successful.</p>
  
  <p>Is this a reasonable plan forward, or am I missing a more common approach?</p>