Repository navigation
Expand file tree
/
Copy pathSqlServer.sql
More file actions
95 lines (87 loc) · 3.6 KB
/
Copy pathSqlServer.sql
File metadata and controls
95 lines (87 loc) · 3.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
/*
SQL Server setup script for Exceptional
Run this script for creating the exceptions table
It will also upgrade a V1 schema to V2, just run the full script.
*/
If Not Exists (Select 1
From INFORMATION_SCHEMA.TABLES
Where [TABLE_SCHEMA] = 'dbo'
And [TABLE_NAME] = 'Exceptions')
Begin
Create Table [dbo].[Exceptions](
[Id] [bigint] Not Null Identity,
[GUID] [uniqueidentifier] Not Null,
[ApplicationName] [nvarchar](50) Not Null,
[MachineName] [nvarchar](50) Not Null,
[CreationDate] [datetime] Not Null,
[Type] [nvarchar](100) Not Null,
[IsProtected] [bit] Not Null Default(0),
[Host] [nvarchar](100) Null,
[Url] [nvarchar](500) Null,
[HTTPMethod] [nvarchar](10) Null,
[IPAddress] [varchar](40) Null,
[Source] [nvarchar](100) Null,
[Message] [nvarchar](1000) Null,
[Detail] [nvarchar](max) Null,
[StatusCode] [int] Null,
[DeletionDate] [datetime] Null,
[FullJson] [nvarchar](max) Null,
[ErrorHash] [int] Null,
[DuplicateCount] [int] Not Null Default(1),
[LastLogDate] [datetime] Null,
[Category] nvarchar(100) Null
Constraint [PK_Exceptions] Primary Key Clustered ([Id] Asc)
With (Pad_Index = Off, Statistics_NoRecompute = Off, Ignore_Dup_Key = Off, Allow_Row_Locks = On, Allow_Page_Locks = On) On [PRIMARY]
);
End
If Not Exists (Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.Exceptions') And name = 'IX_Exceptions_GUID_ApplicationName_DeletionDate_CreationDate')
Begin
Create Unique Nonclustered Index [IX_Exceptions_GUID_ApplicationName_DeletionDate_CreationDate] On [dbo].[Exceptions]
(
[GUID] Asc,
[ApplicationName] Asc,
[DeletionDate] Asc,
[CreationDate] Desc
);
End
If Not Exists (Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.Exceptions') And name = 'IX_Exceptions_ErrorHash_ApplicationName_CreationDate_DeletionDate')
Begin
Create Nonclustered Index [IX_Exceptions_ErrorHash_ApplicationName_CreationDate_DeletionDate] On [dbo].[Exceptions]
(
[ErrorHash] Asc,
[ApplicationName] Asc,
[CreationDate] Desc,
[DeletionDate] Asc
);
End
If Not Exists (Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.Exceptions') And name = 'IX_Exceptions_ApplicationName_DeletionDate_CreationDate_Filtered')
Begin
Create Nonclustered Index [IX_Exceptions_ApplicationName_DeletionDate_CreationDate_Filtered] On [dbo].[Exceptions]
(
[ApplicationName] Asc,
[DeletionDate] Asc,
[CreationDate] Desc
)
Where DeletionDate Is Null;
End
If Not Exists (Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.Exceptions') And name = 'IX_Exceptions_CreationDate_Includes')
Begin
Create Nonclustered Index [IX_Exceptions_CreationDate_Includes] On [dbo].[Exceptions]
(
[CreationDate] Asc
)
Include ([ApplicationName], [MachineName], [DuplicateCount])
End
/* Begin V2 Schema changes */
If Not Exists (Select 1 From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Exceptions' And COLUMN_NAME = 'LastLogDate')
Begin
Alter Table [dbo].[Exceptions] Add [LastLogDate] [datetime] Null;
End
If Not Exists (Select 1 From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Exceptions' And COLUMN_NAME = 'Category')
Begin
Alter Table [dbo].[Exceptions] Add [Category] nvarchar(100) Null;
End
If Exists (Select 1 From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Exceptions' And COLUMN_NAME = 'SQL')
Begin
Alter Table [dbo].[Exceptions] Drop Column [SQL];
End