title: How to clear all system versioned tables in one query?
body: <p>I have a query which allows me to delete all rows and reset all indentity columns of all of my tables without breaking any foreign key references. This query works great for normal SQL Server tables:</p>
  
  <pre class="lang-sql prettyprint-override"><code>EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
  EXEC sp_MSForEachTable 'DELETE FROM ?';
  EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';
  EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED, 0)';
  </code></pre>
  
  <p>The problem is, that now that I'm using system versioned temporal tables, the previous query doesn't work anymore. My versioned tables are all named <code>tbl_Foo</code> and every history table is in the format <code>tbl_Foo_history</code>.</p>
  
  <p>I tried using something like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>EXEC sp_MSForEachTable 'ALTER TABLE ? SET (SYSTEM_VERSIONING = OFF)';
  
  EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
  EXEC sp_MSForEachTable 'DELETE FROM ?';
  EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';
  EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED, 0)';
  
  EXEC sp_MSforeachtable 'ALTER TABLE ? SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ''?''_history))';
  </code></pre>
  
  <p>but it gives the error:</p>
  
  <blockquote>
  <pre><code>SYSTEM_VERSIONING is not turned ON for table 'FOO.dbo.tbl_Foo_history'.
  </code></pre>
  </blockquote>
  
  <p>because the <code>sp_MSForEachTable</code> obviously gets really messed up when trying to remove versioning from a history table.</p>
  
  <p>How should I do this without specifying the queries individually for every single table?</p>