create_azurelogs_tables.sql

/*
Deployment script for azurelogs

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
PRINT N'Creating [dbo].[AzureWebServerLog]...';


GO
CREATE TABLE [dbo].[AzureWebServerLog] (
    [AzureWebServerLogId]         INT            IDENTITY (1, 1) NOT NULL,
    [AzureWebServerLogFileInfoId] INT            NOT NULL,
    [LogFileRowNumber]            INT            NOT NULL,
    [datetime]                    DATETIME2 (2)  NULL,
    [s_sitename]                  VARCHAR (400)  NULL,
    [cs_method]                   VARCHAR (15)   NULL,
    [cs_uri_stem]                 VARCHAR (400)  NULL,
    [cs_uri_query]                NVARCHAR (MAX) NULL,
    [s_port]                      CHAR (6)       NULL,
    [cs_username]                 VARCHAR (400)  NULL,
    [c_ip]                        VARCHAR (50)   NULL,
    [cs_User_Agent]               VARCHAR (MAX)  NULL,
    [cs_Cookie]                   NVARCHAR (MAX) NULL,
    [cs_Referer]                  VARCHAR (MAX)  NULL,
    [cs_host]                     VARCHAR (400)  NULL,
    [sc_status]                   CHAR (6)       NULL,
    [sc_substatus]                CHAR (6)       NULL,
    [sc_win32_status]             CHAR (6)       NULL,
    [sc_bytes]                    INT            NULL,
    [cs_bytes]                    INT            NULL,
    [time_taken]                  INT            NULL,
    PRIMARY KEY CLUSTERED ([AzureWebServerLogId] ASC)
);


GO
PRINT N'Creating [dbo].[AzureWebServerLog].[IX_AzureWebServerLog_AzureWebServerLogFileInfoId]...';


GO
CREATE NONCLUSTERED INDEX [IX_AzureWebServerLog_AzureWebServerLogFileInfoId]
    ON [dbo].[AzureWebServerLog]([AzureWebServerLogFileInfoId] ASC);


GO
PRINT N'Creating [dbo].[AzureWebServerLogFileInfo]...';


GO
CREATE TABLE [dbo].[AzureWebServerLogFileInfo] (
    [AzureWebServerLogFileInfoId] INT           IDENTITY (1, 1) NOT NULL,
    [FileNameAndPath]             VARCHAR (200) NOT NULL,
    [FileName]                    VARCHAR (200) NOT NULL,
    [FileSize]                    BIGINT        NOT NULL,
    [LastModifiedDate]            DATETIME2 (2) NOT NULL,
    PRIMARY KEY CLUSTERED ([AzureWebServerLogFileInfoId] ASC)
);


GO
PRINT N'Creating [dbo].[FK_AzureWebServerLog_AzureWebServerLogFileInfo]...';


GO
ALTER TABLE [dbo].[AzureWebServerLog] WITH NOCHECK
    ADD CONSTRAINT [FK_AzureWebServerLog_AzureWebServerLogFileInfo] FOREIGN KEY ([AzureWebServerLogFileInfoId]) REFERENCES [dbo].[AzureWebServerLogFileInfo] ([AzureWebServerLogFileInfoId]);


GO
PRINT N'Checking existing data against newly created constraints';



GO
ALTER TABLE [dbo].[AzureWebServerLog] WITH CHECK CHECK CONSTRAINT [FK_AzureWebServerLog_AzureWebServerLogFileInfo];


GO
CREATE NONCLUSTERED INDEX [IX_AzureWebServerLogFileInfo_FileNameAndPath]
    ON [dbo].[AzureWebServerLogFileInfo]([FileNameAndPath] ASC);





GO
PRINT N'Done';
GO