Revisions Spec Exercise Steps: Truncate Tables and Drop Indices

1. Open the revisions file to edit.
2. Add a section to truncate tables and drop indexes. For more information, see the example at Revisions Spec: Truncate Tables and Drop Indexes

3. Use "40" to number the ID for that revision.

   <DBRevision ID="40">
   </DBRevision>

4. Within <DBRevision ID="40">, add element to embed Transact-SQL code.

       <ExecuteSql>
         <![CDATA[
       ] ]>
       </ExecuteSql>

   For more information about CDATA sections, see Microsoft's MSDN article at CDATA Sections [XML Standards].

5. Within that, add Transact-SQL code to truncate all extension tables and to drop indexes on all extension tables. For more information about the ALTER PROCEDURE statement, see Microsoft's MSDN article at ALTER PROCEDURE (Transact-SQL).

6. For the purposes of this exercise set, there are only two extension tables:

   1. [FACT_INTERACTIONRESPONSE_EXT]
   2. [FACT_INTERACTIONRESPONSE_EXT_STAGE]

   When you use this revision, ensure you include the truncations and index drops from all previous revisions. An ALTER PROCEDURE statement in this form will overwrite the contents of the stored procedure. We recommend that you maintain a running list of truncations and index drops to paste into these revisions.

7. Add lines to truncate each table and to run the stored procedure to drop indexes for each table.

   For more information about the TRUNCATE TABLE statement, see Microsoft's MSDN article at TRUNCATE TABLE (Transact-SQL). For more information about the EXEC statement, see Microsoft's MSDN article at EXECUTE (Transact-SQL).

   Save your revisions file.

   The revision should look like this:

     <DBRevision ID="40">
       <ExecuteSql>
         <![CDATA[
   alter procedure BBDW.[RESETETL]
   as
     set nocount on;
   
     --Truncate tables in reverse order.
   
     truncate table BBDW.[FACT_INTERACTIONRESPONSE_EXT];
     exec BBDW.[CREATE_OR_DROP_FACT_INTERACTIONRESPONSE_EXT_INDICES] 0;
     truncate table BBDW.[FACT_INTERACTIONRESPONSE_EXT_STAGE];
     exec BBDW.[CREATE_OR_DROP_FACT_INTERACTIONRESPONSE_EXT_STAGE_INDICES] 0;
     ] ]>
       </ExecuteSql>
     </DBRevision>