Page Comparison

declare @dbName nvarchar(MAX);
set @dbName = 'DATAWAREHOUSE_NAME' --Replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database

execute ('CREATE DATABASE ' + @dbName);

execute ('use ' + @dbName + '; ' +
'create table dbo.IC_D_ROUTE (
ID varchar(255) not null,
CODE varchar(255),
EXTERNAL_ID varchar(255),
ROUTE_DATE date,
DOMAIN_CODE varchar(255),
LOGISTICS_STATUS varchar(255),
TRUCK_CODE varchar(255),
DRIVER_CODE varchar(255),
TRAILER_CODE varchar(255),
primary key(ID)
);


create table dbo.IC_M_ROUTE (
ID_ROUTE varchar(255) not null,
TOTAL_DISTANCE_PREDICTED numeric(19,6),
TOTAL_DISTANCE numeric(19,6),
NOMINAL_DIFF_TOTAL_DISTANCE numeric(19,6),
RELATIVE_DIFF_TOTAL_DISTANCE numeric(19,6),
TOTAL_TIME_PREDICTED numeric(19,6),
TOTAL_TIME numeric(19,6),
NOMINAL_DIFF_TOTAL_TIME numeric(19,6),
RELATIVE_DIFF_TOTAL_TIME numeric(19,6),
UV_DELIVERED_PREDICTED numeric(19,6),
UV_DELIVERED numeric(19,6),
NOMINAL_DIFF_UV_DELIVERED numeric(19,6),
RELATIVE_DIFF_UV_DELIVERED numeric(19,6),
UP_DELIVERED_PREDICTED numeric(19,6),
UP_DELIVERED numeric(19,6),
NOMINAL_DIFF_UP_DELIVERED numeric(19,6),
RELATIVE_DIFF_UP_DELIVERED numeric(19,6),
UQ_DELIVERED_PREDICTED numeric(19,6),
UQ_DELIVERED numeric(19,6),
NOMINAL_DIFF_UQ_DELIVERED numeric(19,6),
RELATIVE_DIFF_UQ_DELIVERED numeric(19,6),
UM_DELIVERED_PREDICTED numeric(19,6),
UM_DELIVERED numeric(19,6),
NOMINAL_DIFF_UM_DELIVERED numeric(19,6),
RELATIVE_DIFF_UM_DELIVERED numeric(19,6),
UV_COLLECTED_PREDICTED numeric(19,6),
UV_COLLECTED numeric(19,6),
NOMINAL_DIFF_UV_COLLECTED numeric(19,6),
RELATIVE_DIFF_UV_COLLECTED numeric(19,6),
UP_COLLECTED_PREDICTED numeric(19,6),
UP_COLLECTED numeric(19,6),
NOMINAL_DIFF_UP_COLLECTED numeric(19,6),
RELATIVE_DIFF_UP_COLLECTED numeric(19,6),
UQ_COLLECTED_PREDICTED numeric(19,6),
UQ_COLLECTED numeric(19,6),
NOMINAL_DIFF_UQ_COLLECTED numeric(19,6),
RELATIVE_DIFF_UQ_COLLECTED numeric(19,6),
UM_COLLECTED_PREDICTED numeric(19,6),
UM_COLLECTED numeric(19,6),
NOMINAL_DIFF_UM_COLLECTED numeric(19,6),
RELATIVE_DIFF_UM_COLLECTED numeric(19,6),
START_DATE_PREDICTED datetime,
START_DATE datetime,
NOMINAL_DIFF_START_DATE int,
END_DATE_PREDICTED datetime,
END_DATE datetime,
NOMINAL_DIFF_END_DATE int,
FILLRATE_UV numeric(19,6),
FILLRATE_UV_PREDICTED numeric(19,6),
NOMINAL_DIFF_FILLRATE_UV numeric(19,6),
RELATIVE_DIFF_FILLRATE_UV numeric(19,6),
FILLRATE_UP numeric(19,6),
FILLRATE_UP_PREDICTED numeric(19,6),
NOMINAL_DIFF_FILLRATE_UP numeric(19,6),
RELATIVE_DIFF_FILLRATE_UP numeric(19,6),
#SITES_DELIVERED int,
#SITES_DELIVERED_PREDICTED int,
NOMINAL_DIFF_#SITES_DELIVERED int,
RELATIVE_DIFF_#SITES_DELIVERED numeric(19,6),
#SITES_COLLECTED int,
#SITES_COLLECTED_PREDICTED int,
NOMINAL_DIFF_#SITES_COLLECTED int,
RELATIVE_DIFF_#SITES_COLLECTED numeric(19,6),
#OPERATIONS_DELIVERED int,
#OPERATIONS_DELIVERED_PREDICTED int,
NOMINAL_DIFF_#OPERATIONS_DELIVERED int,
RELATIVE_DIFF_#OPERATIONS_DELIVERED numeric(19,6),
#OPERATIONS_COLLECTED int,
#OPERATIONS_COLLECTED_PREDICTED int,
NOMINAL_DIFF_#OPERATIONS_COLLECTED int,
RELATIVE_DIFF_#OPERATIONS_COLLECTED numeric(19,6),
TRANSPORTATION_COST_PREDICTED numeric(19,6),
TRANSPORTATION_COST numeric(19,6),
NOMINAL_DIFF_TRANSPORTATION_COST numeric(19,6),
RELATIVE_DIFF_TRANSPORTATION_COST numeric(19,6),
primary key(ID_ROUTE)
);


create table dbo.IC_D_SEQUENCE (
ID_ROUTE varchar(255) not null,
ACTIVITY_CODE varchar(255) not null,
SITE_CODE varchar(255) not null,
PASSAGE_INDEX int not null,
ROUTE_ORDER int,
STARTING_SITE_CODE varchar(255),
primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX)
);


create table dbo.IC_D_OPERATION (
ID varchar(255) not null,
DIVISION_INDEX int not null,
ID_ROUTE varchar(255),
CLIENT_SITE_CODE varchar(255),
DEPOSIT_SITE_CODE varchar(255),
PRODUCT_CODE varchar(255),
PACKAGING_CODE varchar(255),
CLIENT_SEQ_ACTIVITY_CODE varchar(255),
CLIENT_SEQ_SITE_CODE varchar(255),
CLIENT_SEQ_PASSAGE_INDEX int,
DEPOSIT_SEQ_ACTIVITY_CODE varchar(255),
DEPOSIT_SEQ_SITE_CODE varchar(255),
DEPOSIT_SEQ_PASSAGE_INDEX int,
primary key(ID, DIVISION_INDEX)
);


create table dbo.IC_M_SEQUENCE (
ID_ROUTE varchar(255) not null,
ACTIVITY_CODE varchar(255) not null,
SITE_CODE varchar(255) not null,
PASSAGE_INDEX int not null,
TOTAL_TIME_PREDICTED int,
TOTAL_TIME int,
NOMINAL_DIFF_TOTAL_TIME int,
RELATIVE_DIFF_TOTAL_TIME numeric(19,6),
UV_PREDICTED numeric(19,6),
UV numeric(19,6),
NOMINAL_DIFF_UV numeric(19,6),
RELATIVE_DIFF_UV numeric(19,6),
UP_PREDICTED numeric(19,6),
UP numeric(19,6),
NOMINAL_DIFF_UP numeric(19,6),
RELATIVE_DIFF_UP numeric(19,6),
UQ_PREDICTED numeric(19,6),
UQ numeric(19,6),
NOMINAL_DIFF_UQ numeric(19,6),
RELATIVE_DIFF_UQ numeric(19,6),
UM_PREDICTED numeric(19,6),
UM numeric(19,6),
NOMINAL_DIFF_UM numeric(19,6),
RELATIVE_DIFF_UM numeric(19,6),
START_DATE_PREDICTED datetime,
START_DATE datetime,
NOMINAL_DIFF_START_DATE int,
END_DATE_PREDICTED datetime,
END_DATE datetime,
NOMINAL_DIFF_END_DATE int,
primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX)
);


create table dbo.IC_M_OPERATION (
ID_OPERATION varchar(255) not null,
DIVISION_INDEX int not null,
UV_PREDICTED numeric(19,6),
UV_INITIAL numeric(19,6),
UV_REALISED numeric(19,6),
NOMINAL_DIFF_UV numeric(19,6),
RELATIVE_DIFF_UV numeric(19,6),
UP_PREDICTED numeric(19,6),
UP_INITIAL numeric(19,6),
UP_REALISED numeric(19,6),
NOMINAL_DIFF_UP numeric(19,6),
RELATIVE_DIFF_UP numeric(19,6),
UQ_PREDICTED numeric(19,6),
UQ_INITIAL numeric(19,6),
UQ_REALISED numeric(19,6),
NOMINAL_DIFF_UQ numeric(19,6),
RELATIVE_DIFF_UQ numeric(19,6),
UM_PREDICTED numeric(19,6),
UM_INITIAL numeric(19,6),
UM_REALISED numeric(19,6),
NOMINAL_DIFF_UM numeric(19,6),
RELATIVE_DIFF_UM numeric(19,6), 
primary key(ID_OPERATION, DIVISION_INDEX)
);

create table dbo.IC_D_LOGISTIC_AREA (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_SITE (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_PRODUCT (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_PROVIDER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_TRUCK (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_DRIVER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

create table dbo.IC_D_TRAILER (
ID varchar(255) not null,
LABEL varchar(255),
primary key(ID)
);

CREATE LOGIN [' + @dbName + '] WITH PASSWORD=N''' + @dbName + ''', 
DEFAULT_DATABASE=[' + @dbName + '], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 

ALTER LOGIN [' + @dbName + '] ENABLE

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @dbName + ']

ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @dbName + ']

CREATE USER ' + @dbName + ' FROM LOGIN ' + @dbName + ' 

ALTER USER ' + @dbName + ' WITH DEFAULT_SCHEMA=[dbo]

EXEC sp_addrolemember ''db_owner'', ''' + @dbName + '''

USE [master]

GRANT CONNECT ANY DATABASE, AUTHENTICATE SERVER, CONNECT SQL, CONNECT ANY DATABASE TO [' + @dbName + '] WITH GRANT OPTION AS sa

RECONFIGURE ; 

USE [' + @dbName + ']

ALTER ROLE [db_owner] ADD MEMBER [' + @dbName + ']

RECONFIGURE ; 
');