text: ﻿SET ANSI_NULLS              ON;
  SET ANSI_PADDING            ON;
  SET ANSI_WARNINGS           ON;
  SET ANSI_NULL_DFLT_ON       ON;
  SET CONCAT_NULL_YIELDS_NULL ON;
  SET QUOTED_IDENTIFIER       ON;
  
  -- Must be executed inside the target database
  
  -- Tables
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='ChangeTracking' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE dbo.ChangeTracking;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='Date' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE dbo.[Date];
  
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='Devices' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE analytics.Devices;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='MeasurementDefinitions' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE analytics.MeasurementDefinitions;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='Measurements' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE analytics.Measurements;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='DeviceTemplates' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE analytics.DeviceTemplates;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='Properties' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE analytics.Properties;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='PropertyDefinitions' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE analytics.PropertyDefinitions;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='analytics' AND TABLE_NAME='Messages' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE analytics.[Messages];
  
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='stage' AND TABLE_NAME='Measurements' AND TABLE_TYPE='BASE TABLE')
      DROP TABLE stage.Measurements;
  
      
  -- Stored Procedures
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='TransformMeasurements' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.TransformMeasurements;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='UpdateMeasurements' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.UpdateMeasurements;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='InsertMeasurements' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.InsertMeasurements;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='InsertDevices' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.InsertDevices;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='InsertProperties' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.InsertProperties;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='InsertDeviceTemplates' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.InsertDeviceTemplates;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='InsertMeasurementDefinitions' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.InsertMeasurementDefinitions;	
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='InsertPropertyDefinitions' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.InsertPropertyDefinitions;
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME='InsertMessages' AND ROUTINE_TYPE='PROCEDURE')
      DROP PROCEDURE dbo.InsertMessages;
  
  -- Table Types	
  IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'MeasurementsTableType')
  	DROP TYPE dbo.MeasurementsTableType;
  IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'DevicesTableType')
  	DROP TYPE dbo.DevicesTableType;
  IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'PropertiesTableType')
  	DROP TYPE dbo.PropertiesTableType;
  IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'DeviceTemplatesTableType')
  	DROP TYPE dbo.DeviceTemplatesTableType;
  IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'MeasurementDefinitionsTableType')
  	DROP TYPE dbo.MeasurementDefinitionsTableType;
  IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'PropertyDefinitionsTableType')
  	DROP TYPE dbo.PropertyDefinitionsTableType;
  IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'MessagesTableType')
  	DROP TYPE dbo.MessagesTableType;
  
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name]='analytics')
  BEGIN
      EXEC ('CREATE SCHEMA analytics AUTHORIZATION dbo'); -- Avoid batch error
  END;
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE [name]='stage')
  BEGIN
      EXEC ('CREATE SCHEMA stage AUTHORIZATION dbo'); -- Avoid batch error
  END;
  