· 8 years ago · May 16, 2018, 09:00 PM
1USE [master]
2GO
3/****** Object: Database [ScreenConnectSite] Script Date: 5/16/2018 4:53:33 PM ******/
4CREATE DATABASE [ScreenConnectSite]
5 CONTAINMENT = NONE
6 ON PRIMARY
7( NAME = N'ScreenConnectSite', FILENAME = N'D:\RDSDBDATA\DATA\ScreenConnectSite.mdf' , SIZE = 2160192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
8 LOG ON
9( NAME = N'ScreenConnectSite_log', FILENAME = N'D:\RDSDBDATA\DATA\ScreenConnectSite_log.ldf' , SIZE = 2614848KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
10GO
11ALTER DATABASE [ScreenConnectSite] SET COMPATIBILITY_LEVEL = 120
12GO
13IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
14begin
15EXEC [ScreenConnectSite].[dbo].[sp_fulltext_database] @action = 'enable'
16end
17GO
18ALTER DATABASE [ScreenConnectSite] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [ScreenConnectSite] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [ScreenConnectSite] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [ScreenConnectSite] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [ScreenConnectSite] SET ARITHABORT OFF
27GO
28ALTER DATABASE [ScreenConnectSite] SET AUTO_CLOSE OFF
29GO
30ALTER DATABASE [ScreenConnectSite] SET AUTO_SHRINK OFF
31GO
32ALTER DATABASE [ScreenConnectSite] SET AUTO_UPDATE_STATISTICS ON
33GO
34ALTER DATABASE [ScreenConnectSite] SET CURSOR_CLOSE_ON_COMMIT OFF
35GO
36ALTER DATABASE [ScreenConnectSite] SET CURSOR_DEFAULT GLOBAL
37GO
38ALTER DATABASE [ScreenConnectSite] SET CONCAT_NULL_YIELDS_NULL OFF
39GO
40ALTER DATABASE [ScreenConnectSite] SET NUMERIC_ROUNDABORT OFF
41GO
42ALTER DATABASE [ScreenConnectSite] SET QUOTED_IDENTIFIER OFF
43GO
44ALTER DATABASE [ScreenConnectSite] SET RECURSIVE_TRIGGERS OFF
45GO
46ALTER DATABASE [ScreenConnectSite] SET DISABLE_BROKER
47GO
48ALTER DATABASE [ScreenConnectSite] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
49GO
50ALTER DATABASE [ScreenConnectSite] SET DATE_CORRELATION_OPTIMIZATION OFF
51GO
52ALTER DATABASE [ScreenConnectSite] SET TRUSTWORTHY OFF
53GO
54ALTER DATABASE [ScreenConnectSite] SET ALLOW_SNAPSHOT_ISOLATION OFF
55GO
56ALTER DATABASE [ScreenConnectSite] SET PARAMETERIZATION SIMPLE
57GO
58ALTER DATABASE [ScreenConnectSite] SET READ_COMMITTED_SNAPSHOT OFF
59GO
60ALTER DATABASE [ScreenConnectSite] SET HONOR_BROKER_PRIORITY OFF
61GO
62ALTER DATABASE [ScreenConnectSite] SET RECOVERY FULL
63GO
64ALTER DATABASE [ScreenConnectSite] SET MULTI_USER
65GO
66ALTER DATABASE [ScreenConnectSite] SET PAGE_VERIFY CHECKSUM
67GO
68ALTER DATABASE [ScreenConnectSite] SET DB_CHAINING OFF
69GO
70ALTER DATABASE [ScreenConnectSite] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
71GO
72ALTER DATABASE [ScreenConnectSite] SET TARGET_RECOVERY_TIME = 0 SECONDS
73GO
74ALTER DATABASE [ScreenConnectSite] SET DELAYED_DURABILITY = DISABLED
75GO
76USE [ScreenConnectSite]
77GO
78/****** Object: User [slinak] Script Date: 5/16/2018 4:53:34 PM ******/
79CREATE USER [slinak] FOR LOGIN [slinak] WITH DEFAULT_SCHEMA=[dbo]
80GO
81/****** Object: User [SCWWWSite] Script Date: 5/16/2018 4:53:34 PM ******/
82CREATE USER [SCWWWSite] FOR LOGIN [SCWWWSite] WITH DEFAULT_SCHEMA=[dbo]
83GO
84/****** Object: User [screporter] Script Date: 5/16/2018 4:53:34 PM ******/
85CREATE USER [screporter] FOR LOGIN [screporter] WITH DEFAULT_SCHEMA=[dbo]
86GO
87/****** Object: User [scadmin] Script Date: 5/16/2018 4:53:34 PM ******/
88CREATE USER [scadmin] FOR LOGIN [scadmin] WITH DEFAULT_SCHEMA=[dbo]
89GO
90/****** Object: User [pnakhwa] Script Date: 5/16/2018 4:53:34 PM ******/
91CREATE USER [pnakhwa] FOR LOGIN [pnakhwa] WITH DEFAULT_SCHEMA=[dbo]
92GO
93/****** Object: User [mreynolds] Script Date: 5/16/2018 4:53:34 PM ******/
94CREATE USER [mreynolds] FOR LOGIN [mreynolds] WITH DEFAULT_SCHEMA=[dbo]
95GO
96/****** Object: User [jturpin] Script Date: 5/16/2018 4:53:34 PM ******/
97CREATE USER [jturpin] FOR LOGIN [jturpin] WITH DEFAULT_SCHEMA=[dbo]
98GO
99/****** Object: User [jmorgan] Script Date: 5/16/2018 4:53:34 PM ******/
100CREATE USER [jmorgan] FOR LOGIN [jmorgan] WITH DEFAULT_SCHEMA=[dbo]
101GO
102/****** Object: User [cwdba] Script Date: 5/16/2018 4:53:34 PM ******/
103CREATE USER [cwdba] FOR LOGIN [cwdba] WITH DEFAULT_SCHEMA=[dbo]
104GO
105/****** Object: User [connectwise] Script Date: 5/16/2018 4:53:34 PM ******/
106CREATE USER [connectwise] FOR LOGIN [connectwise] WITH DEFAULT_SCHEMA=[dbo]
107GO
108/****** Object: User [bburner] Script Date: 5/16/2018 4:53:34 PM ******/
109CREATE USER [bburner] FOR LOGIN [bburner] WITH DEFAULT_SCHEMA=[dbo]
110GO
111/****** Object: User [amccabe] Script Date: 5/16/2018 4:53:34 PM ******/
112CREATE USER [amccabe] FOR LOGIN [amccabe] WITH DEFAULT_SCHEMA=[dbo]
113GO
114/****** Object: DatabaseRole [Sales] Script Date: 5/16/2018 4:53:34 PM ******/
115CREATE ROLE [Sales]
116GO
117ALTER ROLE [db_owner] ADD MEMBER [SCWWWSite]
118GO
119ALTER ROLE [db_datareader] ADD MEMBER [screporter]
120GO
121ALTER ROLE [db_owner] ADD MEMBER [scadmin]
122GO
123ALTER ROLE [db_datareader] ADD MEMBER [mreynolds]
124GO
125ALTER ROLE [db_datareader] ADD MEMBER [jturpin]
126GO
127ALTER ROLE [db_datareader] ADD MEMBER [jmorgan]
128GO
129ALTER ROLE [db_owner] ADD MEMBER [cwdba]
130GO
131ALTER ROLE [db_datareader] ADD MEMBER [connectwise]
132GO
133ALTER ROLE [db_datareader] ADD MEMBER [amccabe]
134GO
135/****** Object: Schema [sales] Script Date: 5/16/2018 4:53:35 PM ******/
136CREATE SCHEMA [sales]
137GO
138/****** Object: UserDefinedTableType [dbo].[GuidList] Script Date: 5/16/2018 4:53:35 PM ******/
139CREATE TYPE [dbo].[GuidList] AS TABLE(
140 [Item] [uniqueidentifier] NOT NULL
141)
142GO
143/****** Object: UserDefinedFunction [dbo].[FormatPhoneNumber] Script Date: 5/16/2018 4:53:35 PM ******/
144SET ANSI_NULLS ON
145GO
146SET QUOTED_IDENTIFIER ON
147GO
148
149CREATE FUNCTION [dbo].[FormatPhoneNumber] (@PhoneNumberString VARCHAR(256))
150RETURNS VARCHAR(256)
151AS
152BEGIN
153 DECLARE @Result VARCHAR(256)
154 SELECT @Result = REPLACE(@PhoneNumberString, ' ', '')
155 SELECT @Result = REPLACE(@Result, '(', '')
156 SELECT @Result = REPLACE(@Result, ')', '')
157 SELECT @Result = REPLACE(@Result, '.', '')
158 SELECT @Result = REPLACE(@Result, '-', '')
159
160 IF LEN(@Result) = 11 AND LEFT(@Result, 1) = '1'
161 SELECT @Result = RIGHT(@Result, 10)
162
163 IF LEN(@Result) <> 10 OR LEFT(@Result, 1) = '1' OR LEFT(@Result, 1) = '0'
164 RETURN '* ' + @PhoneNumberString
165
166 RETURN SUBSTRING(@Result, 1, 3) + '-' +
167 SUBSTRING(@Result, 4, 3) + '-' +
168 SUBSTRING(@Result, 7, 4)
169END
170
171GO
172/****** Object: Table [dbo].[AccessibilityCheck] Script Date: 5/16/2018 4:53:35 PM ******/
173SET ANSI_NULLS ON
174GO
175SET QUOTED_IDENTIFIER ON
176GO
177CREATE TABLE [dbo].[AccessibilityCheck](
178 [InstanceCode] [bigint] NOT NULL,
179 [Timestamp] [datetime] NOT NULL,
180 [Succeeded] [bit] NOT NULL,
181 [IP] [binary](4) NOT NULL,
182 [TimeTakenMilliseconds] [smallint] NOT NULL,
183 [WebServerUri] [varchar](max) NULL,
184 [RelayUri] [varchar](max) NULL,
185 [WebServerExtraData] [varchar](max) NULL,
186 [RelayExtraData] [varchar](max) NULL,
187 [Version] [varchar](100) NULL,
188 CONSTRAINT [PK_AccessibilityCheck] PRIMARY KEY CLUSTERED
189(
190 [InstanceCode] ASC,
191 [Timestamp] ASC
192)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
193) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
194
195GO
196/****** Object: Table [dbo].[Company] Script Date: 5/16/2018 4:53:35 PM ******/
197SET ANSI_NULLS ON
198GO
199SET QUOTED_IDENTIFIER ON
200GO
201CREATE TABLE [dbo].[Company](
202 [CompanyID] [int] IDENTITY(1,1) NOT NULL,
203 [CompanyName] [varchar](50) NOT NULL,
204 [Status] [varchar](50) NULL,
205 [FollowUpDate] [datetime] NULL,
206 [AccountManager] [varchar](100) NULL,
207 CONSTRAINT [PK_Company_1] PRIMARY KEY CLUSTERED
208(
209 [CompanyID] ASC
210)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
211) ON [PRIMARY]
212
213GO
214/****** Object: Table [dbo].[CompanyLead] Script Date: 5/16/2018 4:53:35 PM ******/
215SET ANSI_NULLS ON
216GO
217SET QUOTED_IDENTIFIER ON
218GO
219CREATE TABLE [dbo].[CompanyLead](
220 [CompanyID] [int] NOT NULL,
221 [Timestamp] [datetime] NOT NULL,
222 [EstimatedAmount] [money] NOT NULL,
223 [Certainty] [int] NOT NULL,
224 CONSTRAINT [PK_CompanyLead] PRIMARY KEY CLUSTERED
225(
226 [CompanyID] ASC,
227 [Timestamp] ASC
228)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
229) ON [PRIMARY]
230
231GO
232/****** Object: Table [dbo].[CompanyLicenseSearch] Script Date: 5/16/2018 4:53:35 PM ******/
233SET ANSI_NULLS ON
234GO
235SET QUOTED_IDENTIFIER ON
236GO
237CREATE TABLE [dbo].[CompanyLicenseSearch](
238 [CompanyID] [int] NOT NULL,
239 [LicenseSearchText] [varchar](100) NOT NULL,
240 CONSTRAINT [PK_CompanyLicenseSearch] PRIMARY KEY CLUSTERED
241(
242 [CompanyID] ASC,
243 [LicenseSearchText] ASC
244)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
245) ON [PRIMARY]
246
247GO
248/****** Object: Table [dbo].[CompanyNote] Script Date: 5/16/2018 4:53:35 PM ******/
249SET ANSI_NULLS ON
250GO
251SET QUOTED_IDENTIFIER ON
252GO
253CREATE TABLE [dbo].[CompanyNote](
254 [CompanyID] [int] NOT NULL,
255 [Timestamp] [datetime] NOT NULL,
256 [Note] [varchar](max) NOT NULL,
257 CONSTRAINT [PK_CompanyNote] PRIMARY KEY CLUSTERED
258(
259 [CompanyID] ASC,
260 [Timestamp] ASC
261)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
262) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
263
264GO
265/****** Object: Table [dbo].[CompanyServiceWork] Script Date: 5/16/2018 4:53:35 PM ******/
266SET ANSI_NULLS ON
267GO
268SET QUOTED_IDENTIFIER ON
269GO
270CREATE TABLE [dbo].[CompanyServiceWork](
271 [CompanyID] [int] NOT NULL,
272 [Timestamp] [datetime] NOT NULL,
273 [Price] [money] NOT NULL,
274 [Note] [text] NULL,
275 CONSTRAINT [PK_CompanyServiceWork] PRIMARY KEY CLUSTERED
276(
277 [CompanyID] ASC,
278 [Timestamp] ASC
279)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
280) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
281
282GO
283/****** Object: Table [dbo].[CopyrightInfringement] Script Date: 5/16/2018 4:53:35 PM ******/
284SET ANSI_NULLS ON
285GO
286SET QUOTED_IDENTIFIER ON
287GO
288CREATE TABLE [dbo].[CopyrightInfringement](
289 [Url] [varchar](256) NOT NULL,
290 [CompanyName] [varchar](max) NULL,
291 [ContactName] [varchar](max) NULL,
292 [ContactEmail] [varchar](max) NULL,
293 [ContactAddress] [varchar](max) NULL,
294 [ContactPhone] [varchar](max) NULL,
295 [HomePage] [varchar](max) NULL,
296 [LinkToScreenConnectPage] [varchar](max) NULL,
297 [Notes] [varchar](max) NULL,
298 CONSTRAINT [PK__Copyrigh__C5B21430607251E5] PRIMARY KEY CLUSTERED
299(
300 [Url] ASC
301)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
302) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
303
304GO
305/****** Object: Table [dbo].[Coupon] Script Date: 5/16/2018 4:53:35 PM ******/
306SET ANSI_NULLS ON
307GO
308SET QUOTED_IDENTIFIER ON
309GO
310CREATE TABLE [dbo].[Coupon](
311 [CouponCode] [varchar](256) NOT NULL,
312 [PriceAdjustmentMultiply] [money] NOT NULL,
313 [PriceAdjustmentAdd] [money] NOT NULL,
314 [BeginDate] [datetime2](7) NOT NULL,
315 [EndDate] [datetime2](7) NOT NULL,
316 CONSTRAINT [PK_Coupon] PRIMARY KEY CLUSTERED
317(
318 [CouponCode] ASC
319)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
320) ON [PRIMARY]
321
322GO
323/****** Object: Table [dbo].[EmailHistory] Script Date: 5/16/2018 4:53:35 PM ******/
324SET ANSI_NULLS ON
325GO
326SET QUOTED_IDENTIFIER ON
327GO
328CREATE TABLE [dbo].[EmailHistory](
329 [EmailID] [varchar](100) NOT NULL,
330 [ToEmailAddress] [varchar](100) NOT NULL,
331 [Timestamp] [datetime] NOT NULL,
332 [ViewTimestamp] [datetime] NULL,
333 [Archived] [bit] NOT NULL
334) ON [PRIMARY]
335
336GO
337/****** Object: Table [dbo].[EmailSchedule] Script Date: 5/16/2018 4:53:35 PM ******/
338SET ANSI_NULLS ON
339GO
340SET QUOTED_IDENTIFIER ON
341GO
342CREATE TABLE [dbo].[EmailSchedule](
343 [EmailID] [varchar](100) NOT NULL,
344 [FromEmailAddress] [varchar](100) NOT NULL,
345 [Subject] [varchar](200) NOT NULL,
346 [PlainBody] [varchar](max) NULL,
347 [HtmlBody] [varchar](max) NULL,
348 [ScheduleType] [varchar](100) NOT NULL,
349 [Param1] [sql_variant] NULL,
350 [Param2] [sql_variant] NULL,
351 CONSTRAINT [PK_EmailSchedule] PRIMARY KEY CLUSTERED
352(
353 [EmailID] ASC
354)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
355) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
356
357GO
358/****** Object: Table [dbo].[EmailSubscribe] Script Date: 5/16/2018 4:53:35 PM ******/
359SET ANSI_NULLS ON
360GO
361SET QUOTED_IDENTIFIER ON
362GO
363CREATE TABLE [dbo].[EmailSubscribe](
364 [EmailAddress] [varchar](100) NOT NULL,
365 [Timestamp] [datetime] NOT NULL
366) ON [PRIMARY]
367
368GO
369/****** Object: Table [dbo].[EmailUnsubscribe] Script Date: 5/16/2018 4:53:35 PM ******/
370SET ANSI_NULLS ON
371GO
372SET QUOTED_IDENTIFIER ON
373GO
374CREATE TABLE [dbo].[EmailUnsubscribe](
375 [EmailAddress] [varchar](100) NOT NULL,
376 [Timestamp] [datetime] NOT NULL,
377 [EmailID] [varchar](100) NULL
378) ON [PRIMARY]
379
380GO
381/****** Object: Table [dbo].[Extension] Script Date: 5/16/2018 4:53:35 PM ******/
382SET ANSI_NULLS ON
383GO
384SET QUOTED_IDENTIFIER ON
385GO
386CREATE TABLE [dbo].[Extension](
387 [ExtensionID] [uniqueidentifier] NOT NULL,
388 [AuthorKeyHash] [varbinary](max) NOT NULL,
389 [DisplayRevisionID] [uniqueidentifier] NULL,
390 [ContactEmailAddress] [nvarchar](max) NOT NULL,
391 CONSTRAINT [PK_Extension] PRIMARY KEY CLUSTERED
392(
393 [ExtensionID] ASC
394)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
395) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
396
397GO
398/****** Object: Table [dbo].[ExtensionCategory] Script Date: 5/16/2018 4:53:35 PM ******/
399SET ANSI_NULLS ON
400GO
401SET QUOTED_IDENTIFIER ON
402GO
403CREATE TABLE [dbo].[ExtensionCategory](
404 [CategoryCode] [varchar](10) NOT NULL,
405 [Name] [nvarchar](max) NOT NULL,
406 [DisplayOrder] [int] NOT NULL,
407 CONSTRAINT [PK_ExtensionCategory] PRIMARY KEY CLUSTERED
408(
409 [CategoryCode] ASC
410)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
411) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
412
413GO
414/****** Object: Table [dbo].[ExtensionCategoryExtension] Script Date: 5/16/2018 4:53:35 PM ******/
415SET ANSI_NULLS ON
416GO
417SET QUOTED_IDENTIFIER ON
418GO
419CREATE TABLE [dbo].[ExtensionCategoryExtension](
420 [CategoryCode] [varchar](10) NOT NULL,
421 [ExtensionID] [uniqueidentifier] NOT NULL,
422 CONSTRAINT [PK_ExtensionCategoryExtension] PRIMARY KEY CLUSTERED
423(
424 [CategoryCode] ASC,
425 [ExtensionID] ASC
426)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
427) ON [PRIMARY]
428
429GO
430/****** Object: Table [dbo].[ExtensionReview] Script Date: 5/16/2018 4:53:35 PM ******/
431SET ANSI_NULLS ON
432GO
433SET QUOTED_IDENTIFIER ON
434GO
435CREATE TABLE [dbo].[ExtensionReview](
436 [ExtensionID] [uniqueidentifier] NOT NULL,
437 [InstanceKey] [varchar](256) NOT NULL,
438 [RevisionID] [uniqueidentifier] NOT NULL,
439 [ReviewTime] [datetime2](7) NOT NULL,
440 [ReviewRating] [int] NOT NULL,
441 [ReviewComment] [nvarchar](max) NULL,
442 [ReviewerDisplayName] [nvarchar](max) NULL,
443PRIMARY KEY CLUSTERED
444(
445 [ExtensionID] ASC,
446 [InstanceKey] ASC
447)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
448) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
449
450GO
451/****** Object: Table [dbo].[ExtensionRevision] Script Date: 5/16/2018 4:53:35 PM ******/
452SET ANSI_NULLS ON
453GO
454SET QUOTED_IDENTIFIER ON
455GO
456CREATE TABLE [dbo].[ExtensionRevision](
457 [ExtensionID] [uniqueidentifier] NOT NULL,
458 [RevisionID] [uniqueidentifier] NOT NULL,
459 [UploadTime] [datetime2](7) NOT NULL,
460 [SubmissionTime] [datetime2](7) NOT NULL,
461 [ApprovalTime] [datetime2](7) NOT NULL,
462 [SubmissionNotes] [nvarchar](max) NOT NULL,
463 [ApprovalNotes] [nvarchar](max) NOT NULL,
464 [ApprovalStatus] [int] NOT NULL,
465 [PublishStatus] [int] NOT NULL,
466 [RevisionType] [int] NOT NULL,
467 [Version] [nvarchar](max) NOT NULL,
468 [Name] [nvarchar](max) NOT NULL,
469 [Author] [nvarchar](max) NOT NULL,
470 [ShortDescription] [nvarchar](max) NOT NULL,
471 [PromotionalImageContent] [image] NOT NULL,
472 [IsTemplate] [bit] NOT NULL,
473 [PackageContent] [image] NOT NULL,
474 [IsUserDeleted] [bit] NOT NULL,
475 [IsPremium] [bit] NOT NULL,
476 CONSTRAINT [PK_ExtensionRevision] PRIMARY KEY CLUSTERED
477(
478 [ExtensionID] ASC,
479 [RevisionID] ASC
480)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
481) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
482
483GO
484/****** Object: Table [dbo].[ForumTopicStatus] Script Date: 5/16/2018 4:53:35 PM ******/
485SET ANSI_NULLS ON
486GO
487SET QUOTED_IDENTIFIER ON
488GO
489CREATE TABLE [dbo].[ForumTopicStatus](
490 [TopicID] [int] NOT NULL,
491 [Assigned] [varchar](20) NULL,
492 [Marked] [char](1) NULL,
493 [MarkTime] [datetime] NULL,
494 [Note] [varchar](max) NULL,
495PRIMARY KEY CLUSTERED
496(
497 [TopicID] ASC
498)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
499) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
500
501GO
502/****** Object: Table [dbo].[License] Script Date: 5/16/2018 4:53:35 PM ******/
503SET ANSI_NULLS ON
504GO
505SET QUOTED_IDENTIFIER ON
506GO
507CREATE TABLE [dbo].[License](
508 [LicenseID] [varchar](100) NOT NULL,
509 [Type] [varchar](100) NOT NULL,
510 [Licensee] [varchar](200) NOT NULL,
511 [Version] [varchar](20) NULL,
512 [ExpirationDate] [datetime] NULL,
513 [InstanceCount] [int] NULL,
514 [Timestamp] [datetime] NOT NULL,
515 [FirstAuditTime] [datetime] NULL,
516 [OrderID] [bigint] NULL,
517 CONSTRAINT [PK_License] PRIMARY KEY CLUSTERED
518(
519 [LicenseID] ASC
520)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
521) ON [PRIMARY]
522
523GO
524/****** Object: Table [dbo].[LicenseAudit] Script Date: 5/16/2018 4:53:35 PM ******/
525SET ANSI_NULLS ON
526GO
527SET QUOTED_IDENTIFIER ON
528GO
529CREATE TABLE [dbo].[LicenseAudit](
530 [LicenseID] [varchar](100) NOT NULL,
531 [Timestamp] [datetime] NOT NULL,
532 [InstanceCode] [bigint] NOT NULL,
533 [InstanceCount] [int] NOT NULL,
534 [IP] [binary](4) NOT NULL,
535 CONSTRAINT [PK_LicenseAudit] PRIMARY KEY CLUSTERED
536(
537 [LicenseID] ASC,
538 [Timestamp] ASC
539)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
540) ON [PRIMARY]
541
542GO
543/****** Object: Table [dbo].[LicensePurchase] Script Date: 5/16/2018 4:53:35 PM ******/
544SET ANSI_NULLS ON
545GO
546SET QUOTED_IDENTIFIER ON
547GO
548CREATE TABLE [dbo].[LicensePurchase](
549 [LicenseID] [varchar](100) NOT NULL,
550 [Timestamp] [datetime] NOT NULL,
551 [EmailAddress] [varchar](100) NULL,
552 [LicenseAmount] [money] NOT NULL,
553 [UpgradeCreditAmount] [money] NOT NULL,
554 [UpgradePortionAmount] [money] NOT NULL,
555 [DiscountAmount] [money] NOT NULL,
556 [TaxAmount] [money] NOT NULL,
557 [TotalCustomerAmount] [money] NOT NULL,
558 [FeesAmount] [money] NOT NULL,
559 [Source] [varchar](20) NULL,
560 CONSTRAINT [PK_LicensePurchase] PRIMARY KEY CLUSTERED
561(
562 [LicenseID] ASC,
563 [Timestamp] ASC
564)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
565) ON [PRIMARY]
566
567GO
568/****** Object: Table [dbo].[LicenseRevocation] Script Date: 5/16/2018 4:53:35 PM ******/
569SET ANSI_NULLS ON
570GO
571SET QUOTED_IDENTIFIER ON
572GO
573CREATE TABLE [dbo].[LicenseRevocation](
574 [LicenseID] [varchar](100) NOT NULL,
575 [Reason] [int] NOT NULL,
576 [IP] [binary](4) NOT NULL,
577 [Timestamp] [datetime] NOT NULL,
578 [EffectiveTime] [datetime] NULL
579) ON [PRIMARY]
580
581GO
582SET ANSI_PADDING ON
583
584GO
585/****** Object: Index [IX_LicenseID] Script Date: 5/16/2018 4:53:35 PM ******/
586CREATE CLUSTERED INDEX [IX_LicenseID] ON [dbo].[LicenseRevocation]
587(
588 [LicenseID] ASC
589)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
590GO
591/****** Object: Table [dbo].[LicenseRevocationCallback] Script Date: 5/16/2018 4:53:35 PM ******/
592SET ANSI_NULLS ON
593GO
594SET QUOTED_IDENTIFIER ON
595GO
596CREATE TABLE [dbo].[LicenseRevocationCallback](
597 [LicenseID] [varchar](100) NOT NULL,
598 [Timestamp] [datetime] NOT NULL,
599 [InstanceCode] [bigint] NOT NULL,
600 [IP] [binary](4) NOT NULL,
601 CONSTRAINT [PK_LicenseRevocationCallback_1] PRIMARY KEY CLUSTERED
602(
603 [LicenseID] ASC,
604 [Timestamp] ASC
605)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
606) ON [PRIMARY]
607
608GO
609/****** Object: Table [dbo].[LinkBuildIgnoreURL] Script Date: 5/16/2018 4:53:35 PM ******/
610SET ANSI_NULLS ON
611GO
612SET QUOTED_IDENTIFIER ON
613GO
614CREATE TABLE [dbo].[LinkBuildIgnoreURL](
615 [URLPattern] [varchar](200) NULL
616) ON [PRIMARY]
617
618GO
619/****** Object: Table [dbo].[LinkBuildNote] Script Date: 5/16/2018 4:53:35 PM ******/
620SET ANSI_NULLS ON
621GO
622SET QUOTED_IDENTIFIER ON
623GO
624CREATE TABLE [dbo].[LinkBuildNote](
625 [LinkBuildSiteID] [int] NOT NULL,
626 [Timestamp] [datetime] NOT NULL,
627 [Note] [varchar](max) NOT NULL,
628 [WhoAdded] [varchar](100) NULL,
629 CONSTRAINT [PK_LinkBuildNote] PRIMARY KEY CLUSTERED
630(
631 [LinkBuildSiteID] ASC,
632 [Timestamp] ASC
633)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
634) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
635
636GO
637/****** Object: Table [dbo].[LinkBuildSite] Script Date: 5/16/2018 4:53:35 PM ******/
638SET ANSI_NULLS ON
639GO
640SET QUOTED_IDENTIFIER ON
641GO
642CREATE TABLE [dbo].[LinkBuildSite](
643 [LinkBuildSiteID] [int] IDENTITY(1,1) NOT NULL,
644 [Name] [varchar](200) NOT NULL,
645 [MainURL] [varchar](200) NOT NULL,
646 [WhoAdded] [varchar](100) NULL,
647 [Type] [char](2) NULL,
648 [Prominence] [int] NULL,
649 [EmailAddress] [varchar](100) NULL,
650 CONSTRAINT [PK_LinkBuildSite] PRIMARY KEY CLUSTERED
651(
652 [LinkBuildSiteID] ASC
653)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
654) ON [PRIMARY]
655
656GO
657/****** Object: Table [dbo].[LinkBuildSiteType] Script Date: 5/16/2018 4:53:35 PM ******/
658SET ANSI_NULLS ON
659GO
660SET QUOTED_IDENTIFIER ON
661GO
662CREATE TABLE [dbo].[LinkBuildSiteType](
663 [Type] [char](2) NOT NULL,
664 [Description] [varchar](100) NOT NULL
665) ON [PRIMARY]
666
667GO
668/****** Object: Table [dbo].[LinkBuildURL] Script Date: 5/16/2018 4:53:35 PM ******/
669SET ANSI_NULLS ON
670GO
671SET QUOTED_IDENTIFIER ON
672GO
673CREATE TABLE [dbo].[LinkBuildURL](
674 [LinkBuildSiteID] [int] NOT NULL,
675 [Timestamp] [datetime] NOT NULL,
676 [URL] [varchar](200) NOT NULL,
677 [Type] [char](2) NOT NULL,
678 [WhoAdded] [varchar](100) NULL,
679 CONSTRAINT [PK_LinkBuildURL] PRIMARY KEY CLUSTERED
680(
681 [LinkBuildSiteID] ASC,
682 [Timestamp] ASC
683)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
684) ON [PRIMARY]
685
686GO
687/****** Object: Table [dbo].[LinkBuildURLType] Script Date: 5/16/2018 4:53:35 PM ******/
688SET ANSI_NULLS ON
689GO
690SET QUOTED_IDENTIFIER ON
691GO
692CREATE TABLE [dbo].[LinkBuildURLType](
693 [Type] [char](2) NOT NULL,
694 [Description] [varchar](100) NOT NULL,
695 CONSTRAINT [PK_LinkBuildURLType] PRIMARY KEY CLUSTERED
696(
697 [Type] ASC
698)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
699) ON [PRIMARY]
700
701GO
702/****** Object: Table [dbo].[Order] Script Date: 5/16/2018 4:53:35 PM ******/
703SET ANSI_NULLS ON
704GO
705SET QUOTED_IDENTIFIER ON
706GO
707CREATE TABLE [dbo].[Order](
708 [OrderID] [bigint] NOT NULL,
709 [Status] [int] NOT NULL,
710 [TotalAmount] [money] NOT NULL,
711 [UpgradePortionAmount] [money] NOT NULL,
712 [DiscountAmount] [money] NOT NULL,
713 [EmailAddress] [nvarchar](max) NOT NULL,
714 [CompanyOrganization] [nvarchar](max) NOT NULL,
715 [FirstName] [nvarchar](max) NOT NULL,
716 [LastName] [nvarchar](max) NOT NULL,
717 [Address] [nvarchar](max) NOT NULL,
718 [City] [nvarchar](max) NOT NULL,
719 [StateProvince] [nvarchar](max) NOT NULL,
720 [ZipPostalCode] [nvarchar](max) NOT NULL,
721 [Country] [nvarchar](max) NOT NULL,
722 [PurchaseOrderNumber] [nvarchar](max) NOT NULL,
723 [ValueAddedTaxNumber] [nvarchar](max) NOT NULL,
724 [AnalyticsClientID] [nvarchar](max) NOT NULL,
725 [Timestamp] [datetime2](7) NOT NULL,
726 [PhoneNumber] [nvarchar](max) NOT NULL,
727 [TaxAmount] [money] NOT NULL,
728 CONSTRAINT [PK__Order__C3905BAF3C34F16F] PRIMARY KEY CLUSTERED
729(
730 [OrderID] ASC
731)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
732) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
733
734GO
735/****** Object: Table [dbo].[OrderLineItem] Script Date: 5/16/2018 4:53:35 PM ******/
736SET ANSI_NULLS ON
737GO
738SET QUOTED_IDENTIFIER ON
739GO
740CREATE TABLE [dbo].[OrderLineItem](
741 [OrderID] [bigint] NOT NULL,
742 [Ordinal] [int] NOT NULL,
743 [ItemType] [tinyint] NOT NULL,
744 [ItemCode] [nvarchar](max) NOT NULL,
745 [ItemDescription] [nvarchar](max) NOT NULL,
746 [Quantity] [int] NOT NULL,
747 [UnitPrice] [money] NOT NULL,
748 [Price] [money] NOT NULL
749) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
750
751GO
752/****** Object: Table [dbo].[OrderTransaction] Script Date: 5/16/2018 4:53:35 PM ******/
753SET ANSI_NULLS ON
754GO
755SET QUOTED_IDENTIFIER ON
756GO
757CREATE TABLE [dbo].[OrderTransaction](
758 [OrderID] [bigint] NULL,
759 [Method] [int] NULL,
760 [Amount] [money] NULL,
761 [TransactionID] [varchar](max) NULL,
762 [Timestamp] [datetime2](7) NULL
763) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
764
765GO
766/****** Object: Table [dbo].[PaymentNotification] Script Date: 5/16/2018 4:53:35 PM ******/
767SET ANSI_NULLS ON
768GO
769SET QUOTED_IDENTIFIER ON
770GO
771CREATE TABLE [dbo].[PaymentNotification](
772 [OrderNumber] [varchar](max) NOT NULL,
773 [NotificationType] [varchar](max) NOT NULL,
774 [Xml] [xml] NOT NULL,
775 [Timestamp] [datetime] NOT NULL
776) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
777
778GO
779/****** Object: Table [dbo].[SiteSearch] Script Date: 5/16/2018 4:53:35 PM ******/
780SET ANSI_NULLS ON
781GO
782SET QUOTED_IDENTIFIER ON
783GO
784CREATE TABLE [dbo].[SiteSearch](
785 [Keyword] [varchar](100) NOT NULL,
786 [Page] [varchar](100) NOT NULL,
787 [Timestamp] [datetime] NOT NULL
788) ON [PRIMARY]
789
790GO
791/****** Object: Table [dbo].[Table] Script Date: 5/16/2018 4:53:35 PM ******/
792SET ANSI_NULLS ON
793GO
794SET QUOTED_IDENTIFIER ON
795GO
796CREATE TABLE [dbo].[Table](
797 [ExtensionID] [uniqueidentifier] NOT NULL,
798 [Revison] [uniqueidentifier] NOT NULL,
799 [InstanceKey] [nvarchar](50) NOT NULL,
800 [ReviewTime] [datetime2](7) NOT NULL,
801 [ReviewRating] [int] NOT NULL,
802 [ReviewComment] [nvarchar](4000) NULL,
803PRIMARY KEY CLUSTERED
804(
805 [ExtensionID] ASC,
806 [InstanceKey] ASC,
807 [Revison] ASC
808)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
809) ON [PRIMARY]
810
811GO
812/****** Object: Table [dbo].[TrialExtension] Script Date: 5/16/2018 4:53:35 PM ******/
813SET ANSI_NULLS ON
814GO
815SET QUOTED_IDENTIFIER ON
816GO
817CREATE TABLE [dbo].[TrialExtension](
818 [IP] [binary](4) NOT NULL,
819 [ExtensionDate] [datetime] NOT NULL,
820 [Note] [varchar](max) NULL
821) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
822
823GO
824/****** Object: Table [dbo].[TrialRequest] Script Date: 5/16/2018 4:53:35 PM ******/
825SET ANSI_NULLS ON
826GO
827SET QUOTED_IDENTIFIER ON
828GO
829CREATE TABLE [dbo].[TrialRequest](
830 [LicenseID] [varchar](100) NOT NULL,
831 [IP] [binary](4) NOT NULL,
832 [AnalyticsSource] [varchar](100) NULL,
833 [AnalyticsMedium] [varchar](100) NULL,
834 [AnalyticsCampaign] [varchar](100) NULL,
835 [AnalyticsKeyword] [varchar](100) NULL,
836 [Timestamp] [datetime] NOT NULL,
837 [EmailAddress] [varchar](100) NULL,
838 [FullName] [varchar](100) NULL,
839 [PhoneNumber] [varchar](100) NULL,
840 [LastFollowUpDate] [datetime] NULL,
841 [IsBogus] [bit] NOT NULL,
842 [Note] [varchar](max) NULL,
843 [IsThirdParty] [bit] NOT NULL,
844 CONSTRAINT [PK_TrialRequest] PRIMARY KEY CLUSTERED
845(
846 [LicenseID] ASC
847)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
848) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
849
850GO
851/****** Object: Table [dbo].[VersionCallback] Script Date: 5/16/2018 4:53:35 PM ******/
852SET ANSI_NULLS ON
853GO
854SET QUOTED_IDENTIFIER ON
855GO
856CREATE TABLE [dbo].[VersionCallback](
857 [IP] [binary](4) NOT NULL,
858 [Version] [varchar](100) NOT NULL,
859 [LicenseID] [varchar](100) NOT NULL,
860 [Platform] [tinyint] NOT NULL,
861 [Timestamp] [datetime] NOT NULL,
862 [IPFirst3Octets] AS (substring([IP],(1),(3))),
863 [Archived] [bit] NOT NULL
864) ON [PRIMARY]
865
866GO
867/****** Object: Table [dbo].[WebArchive] Script Date: 5/16/2018 4:53:35 PM ******/
868SET ANSI_NULLS ON
869GO
870SET QUOTED_IDENTIFIER ON
871GO
872CREATE TABLE [dbo].[WebArchive](
873 [Url] [varchar](max) NOT NULL,
874 [ArchiveUrl] [varchar](max) NOT NULL,
875 [Timestamp] [datetime] NOT NULL
876) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
877
878GO
879/****** Object: Table [dbo].[WebCapture] Script Date: 5/16/2018 4:53:35 PM ******/
880SET ANSI_NULLS ON
881GO
882SET QUOTED_IDENTIFIER ON
883GO
884CREATE TABLE [dbo].[WebCapture](
885 [Url] [varchar](256) NOT NULL,
886 [Type] [char](1) NOT NULL,
887 [Timestamp] [datetime] NOT NULL,
888 [DataHash] [varchar](40) NULL,
889 [PublicKeyTokenString] [varchar](max) NULL,
890 [ServerHeaderValue] [varchar](max) NULL
891) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
892
893GO
894/****** Object: Table [dbo].[WebData] Script Date: 5/16/2018 4:53:35 PM ******/
895SET ANSI_NULLS ON
896GO
897SET QUOTED_IDENTIFIER ON
898GO
899CREATE TABLE [dbo].[WebData](
900 [DataHash] [varchar](40) NOT NULL,
901 [Data] [varbinary](max) NULL,
902PRIMARY KEY CLUSTERED
903(
904 [DataHash] ASC
905)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
906) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
907
908GO
909/****** Object: View [dbo].[AllEmailAddresses] Script Date: 5/16/2018 4:53:35 PM ******/
910SET ANSI_NULLS ON
911GO
912SET QUOTED_IDENTIFIER ON
913GO
914create view [dbo].[AllEmailAddresses] as
915select emailaddress from [order]
916union select emailaddress from licensepurchase
917union select emailaddress from trialrequest
918
919GO
920/****** Object: View [dbo].[AllEmailAddressesWithDomain] Script Date: 5/16/2018 4:53:35 PM ******/
921SET ANSI_NULLS ON
922GO
923SET QUOTED_IDENTIFIER ON
924GO
925
926
927create view [dbo].[AllEmailAddressesWithDomain] as
928select EmailAddress, SUBSTRING(emailaddress, CHARINDEX('@', emailaddress ) + 1, 1000) as EmailDomain from AllEmailAddresses
929
930GO
931/****** Object: View [dbo].[CompanyLicense] Script Date: 5/16/2018 4:53:35 PM ******/
932SET ANSI_NULLS ON
933GO
934SET QUOTED_IDENTIFIER ON
935GO
936CREATE VIEW [dbo].[CompanyLicense] AS
937WITH TrialLicense AS
938(
939 SELECT License.*, AnalyticsSource, FullName, PhoneNumber FROM License LEFT OUTER JOIN TrialRequest ON License.LicenseID = TrialRequest.LicenseID
940)
941SELECT a.CompanyID, c.*
942FROM Company a
943INNER JOIN CompanyLicenseSearch b ON a.CompanyID = b.CompanyID
944RIGHT OUTER JOIN TrialLicense c ON
945 LicenseID LIKE '%' + b.LicenseSearchText + '%' OR
946 Licensee LIKE '%' + b.LicenseSearchText + '%' OR
947 FullName LIKE '%' + b.LicenseSearchText + '%' OR
948 PhoneNumber LIKE '%' + b.LicenseSearchText + '%' OR
949 AnalyticsSource LIKE '%' + b.LicenseSearchText + '%'
950
951
952GO
953/****** Object: View [dbo].[CompanylessTrialPhoneNumber] Script Date: 5/16/2018 4:53:35 PM ******/
954SET ANSI_NULLS ON
955GO
956SET QUOTED_IDENTIFIER ON
957GO
958CREATE VIEW [dbo].[CompanylessTrialPhoneNumber] AS
959SELECT
960 Licensee,
961 CAST(TrialRequest.Timestamp AS DATE) [Date],
962 (SELECT COUNT(*) FROM License l WHERE l.Licensee = License.Licensee) EmailTrials,
963 (COALESCE(AnalyticsSource, '') + '-' + COALESCE(AnalyticsMedium, '') + '-' + COALESCE(AnalyticsCampaign, '') + '-' + COALESCE(AnalyticsKeyword, '')) Analytics,
964 FullName,
965 dbo.FormatPhoneNumber(PhoneNumber)PhoneNumber ,
966 (SELECT CAST(MIN(Timestamp) AS DATE) FROM LicenseAudit WHERE LicenseID = License.LicenseID) FirstSession,
967 (SELECT CAST(MAX(Timestamp) AS DATE) FROM LicenseAudit WHERE LicenseID = License.LicenseID) LastSession,
968 (SELECT COUNT(*) FROM LicenseAudit WHERE LicenseID = License.LicenseID) NumSession,
969 LastFollowUpDate LastFollowUp,
970 IsBogus,
971 Note
972FROM TrialRequest INNER JOIN License ON TrialRequest.LicenseID = License.LicenseID
973WHERE NOT PhoneNumber IS NULL AND
974NOT License.LicenseID IN (SELECT LicenseID FROM CompanyLicense WHERE NOT CompanyID IS NULL) AND
975IsBogus = 0 AND
976TrialRequest.Timestamp > GETDATE() - 90
977
978
979
980
981GO
982/****** Object: View [dbo].[WebCaptureStatus] Script Date: 5/16/2018 4:53:35 PM ******/
983SET ANSI_NULLS ON
984GO
985SET QUOTED_IDENTIFIER ON
986GO
987
988CREATE VIEW [dbo].[WebCaptureStatus] AS
989SELECT
990 Url,
991 Type,
992 MaxTimestamp,
993 (SELECT DataHash FROM WebCapture WHERE Url = a.Url AND Type = a.Type AND Timestamp = a.MaxTimestamp) DataHash,
994 (SELECT PublicKeyTokenString FROM WebCapture WHERE Url = a.Url AND Type = a.Type AND Timestamp = a.MaxTimestamp) PublicKeyTokenString,
995 (SELECT ServerHeaderValue FROM WebCapture WHERE Url = a.Url AND Type = a.Type AND Timestamp = a.MaxTimestamp) ServerHeaderValue,
996 (SELECT (CASE WHEN COUNT(*) = 0 THEN 'S' ELSE 'F' + CAST(COUNT(*) AS VARCHAR) END) FROM WebCapture b WHERE b.Url = a.Url AND b.Type = a.Type AND DataHash IS NULL AND Timestamp > (SELECT COALESCE(MAX(Timestamp), 0) FROM WebCapture c WHERE c.Url = b.Url AND c.Type = b.Type AND NOT DataHash IS NULL)) Status
997FROM
998 (SELECT Url, Type, MAX(Timestamp) MaxTimestamp FROM WebCapture GROUP BY Url, Type) a
999
1000
1001GO
1002/****** Object: View [dbo].[CopyrightInfringementWork] Script Date: 5/16/2018 4:53:35 PM ******/
1003SET ANSI_NULLS ON
1004GO
1005SET QUOTED_IDENTIFIER ON
1006GO
1007CREATE view [dbo].[CopyrightInfringementWork] AS
1008SELECT
1009a.Url,
1010b.Status UrlStatus,
1011(CASE
1012 WHEN e.PublicKeyTokenString IS NULL THEN ''
1013 WHEN e.PublicKeyTokenString <> '4b14c015c87c1ad8' THEN 'I'
1014 ELSE 'L'
1015END) InfringmentStatus,
1016CompanyName,
1017ContactName,
1018ContactEmail,
1019ContactAddress,
1020ContactPhone,
1021HomePage,
1022c.Status HomePageStatus,
1023LinkToScreenConnectPage,
1024d.Status LinkToScreenConnectPageStatus,
1025Notes
1026FROM CopyrightInfringement a
1027LEFT OUTER JOIN WebCaptureStatus b ON b.Url = a.Url AND b.Type = 'T'
1028LEFT OUTER JOIN WebCaptureStatus c ON c.Url = a.HomePage AND c.Type = 'T'
1029LEFT OUTER JOIN WebCaptureStatus d ON d.Url = a.LinkToScreenConnectPage AND d.Type = 'T'
1030LEFT OUTER JOIN WebCaptureStatus e ON e.Url = a.Url + 'Bin/Elsinore.ScreenConnect.Server.dll' AND b.Type = 'T'
1031
1032GO
1033/****** Object: View [dbo].[CopyrightInfringementActiveWork] Script Date: 5/16/2018 4:53:35 PM ******/
1034SET ANSI_NULLS ON
1035GO
1036SET QUOTED_IDENTIFIER ON
1037GO
1038
1039CREATE view [dbo].[CopyrightInfringementActiveWork] AS
1040SELECT * FROM CopyrightInfringementWork WHERE UrlStatus = 'S' AND InfringmentStatus = 'I'
1041
1042GO
1043/****** Object: View [dbo].[LicenseIP] Script Date: 5/16/2018 4:53:35 PM ******/
1044SET ANSI_NULLS ON
1045GO
1046SET QUOTED_IDENTIFIER ON
1047GO
1048CREATE VIEW [dbo].[LicenseIP] AS
1049SELECT DISTINCT LicenseID, IP FROM TrialRequest
1050UNION
1051SELECT DISTINCT LicenseID, IP FROM LicenseRevocationCallback
1052UNION
1053SELECT DISTINCT LicenseID, IP FROM VersionCallback
1054
1055GO
1056/****** Object: View [dbo].[RelatedLicense] Script Date: 5/16/2018 4:53:35 PM ******/
1057SET ANSI_NULLS ON
1058GO
1059SET QUOTED_IDENTIFIER ON
1060GO
1061CREATE VIEW [dbo].[RelatedLicense] AS
1062WITH a AS
1063(
1064 SELECT DISTINCT LicenseID, SUBSTRING(IP, 1, 3) ClassC FROM LicenseIP
1065)
1066SELECT b.LicenseID LicenseID1, c.LicenseID LicenseID2 FROM a b INNER JOIN a c on b.ClassC = c.ClassC
1067
1068GO
1069/****** Object: View [dbo].[AllScheduledEmail] Script Date: 5/16/2018 4:53:35 PM ******/
1070SET ANSI_NULLS ON
1071GO
1072SET QUOTED_IDENTIFIER ON
1073GO
1074
1075
1076
1077
1078CREATE VIEW [dbo].[AllScheduledEmail] AS
1079(SELECT EmailSchedule.*, EmailAddress ToEmailAddress, TrialRecipient.Timestamp + CAST(Param2 AS INT) EndDate FROM EmailSchedule, (SELECT EmailAddress, Timestamp FROM TrialRequest) TrialRecipient WHERE ScheduleType = 'AfterTrialWithoutPurchase' AND TrialRecipient.Timestamp BETWEEN GETDATE() - CAST(Param2 AS INT) AND GETDATE() - CAST(Param1 AS INT))
1080UNION ALL
1081(SELECT EmailSchedule.*, EmailAddress ToEmailAddress, TrialRecipient.Timestamp + CAST(Param2 AS INT) EndDate FROM EmailSchedule, (SELECT EmailAddress, TrialRequest.Timestamp FROM TrialRequest INNER JOIN License ON TrialRequest.LicenseID = License.LicenseID WHERE FirstAuditTime IS NULL) TrialRecipient WHERE ScheduleType = 'AfterTrialWithoutSession' AND TrialRecipient.Timestamp BETWEEN GETDATE() - CAST(Param2 AS INT) AND GETDATE() - CAST(Param1 AS INT))
1082UNION ALL
1083(SELECT EmailSchedule.*, EmailAddress ToEmailAddress, PurchaseRecipient.Timestamp + CAST(Param2 AS INT) EndDate FROM EmailSchedule, (SELECT EmailAddress, Timestamp FROM LicensePurchase) PurchaseRecipient WHERE ScheduleType = 'AfterPurchase' AND PurchaseRecipient.Timestamp BETWEEN GETDATE() - CAST(Param2 AS INT) AND GETDATE() - CAST(Param1 AS INT))
1084UNION ALL
1085(SELECT EmailSchedule.*, CAST(EmailAddress AS VARCHAR(100)) ToEmailAddress, CAST(PurchaseRecipient.Timestamp AS DATETIME) + CAST(Param2 AS INT) EndDate FROM EmailSchedule, (SELECT EmailAddress, Timestamp FROM [Order] WHERE Status = 1) PurchaseRecipient WHERE ScheduleType = 'AfterPurchase' AND PurchaseRecipient.Timestamp BETWEEN GETDATE() - CAST(Param2 AS INT) AND GETDATE() - CAST(Param1 AS INT))
1086UNION ALL
1087(SELECT EmailSchedule.*, EmailAddress ToEmailAddress, CAST(Param2 AS DATETIME) EndDate FROM (SELECT * FROM EmailSchedule WHERE ScheduleType = 'OneTime' AND GETDATE() BETWEEN CAST(Param1 AS DATETIME) AND CAST(Param2 AS DATETIME)) EmailSchedule, (SELECT EmailAddress FROM TrialRequest WHERE NOT EmailAddress IS NULL) TrialRecipient)
1088UNION ALL
1089(SELECT EmailSchedule.*, EmailAddress ToEmailAddress, CAST(Param2 AS DATETIME) EndDate FROM (SELECT * FROM EmailSchedule WHERE ScheduleType = 'OneTime' AND GETDATE() BETWEEN CAST(Param1 AS DATETIME) AND CAST(Param2 AS DATETIME)) EmailSchedule, (SELECT EmailAddress FROM EmailSubscribe WHERE NOT EmailAddress IS NULL) EmailSubscriber)
1090UNION ALL
1091(SELECT EmailSchedule.*, EmailAddress ToEmailAddress, CAST(Param2 AS DATETIME) EndDate FROM (SELECT * FROM EmailSchedule WHERE ScheduleType = 'OneTime' AND GETDATE() BETWEEN CAST(Param1 AS DATETIME) AND CAST(Param2 AS DATETIME)) EmailSchedule, (SELECT EmailAddress FROM LicensePurchase WHERE NOT EmailAddress IS NULL) PurchaseRecipient)
1092UNION ALL
1093(SELECT EmailSchedule.*, CAST(EmailAddress AS VARCHAR(100)) ToEmailAddress, CAST(Param2 AS DATETIME) EndDate FROM (SELECT * FROM EmailSchedule WHERE ScheduleType = 'OneTime' AND GETDATE() BETWEEN CAST(Param1 AS DATETIME) AND CAST(Param2 AS DATETIME)) EmailSchedule, (SELECT EmailAddress FROM [Order] WHERE NOT EmailAddress IS NULL) PurchaseRecipient)
1094
1095
1096
1097
1098
1099GO
1100/****** Object: View [dbo].[LinkBuildURLSummary] Script Date: 5/16/2018 4:53:35 PM ******/
1101SET ANSI_NULLS ON
1102GO
1103SET QUOTED_IDENTIFIER ON
1104GO
1105CREATE VIEW [dbo].[LinkBuildURLSummary] AS
1106SELECT
1107 a.Name, b.URL, b.Type, c.Description, b.Timestamp
1108FROM
1109 LinkBuildSite a INNER JOIN LinkBuildURL b ON a.LinkBuildSiteID = b.LinkBuildSiteID
1110 LEFT OUTER JOIN LinkBuildURLType c ON b.Type = c.Type
1111
1112GO
1113/****** Object: View [dbo].[TrialLicenseAnalysis] Script Date: 5/16/2018 4:53:35 PM ******/
1114SET ANSI_NULLS ON
1115GO
1116SET QUOTED_IDENTIFIER ON
1117GO
1118
1119
1120CREATE VIEW [dbo].[TrialLicenseAnalysis] AS
1121SELECT
1122 tr.LicenseID,
1123 tr.Timestamp,
1124 lrc.InstanceCode,
1125 lrc.Timestamp FirstLicenseRevocationCallbackTimestamp,
1126 lrc2.PreviousLicenseCount,
1127 ac.Timestamp AccessibilityCheckTimestamp,
1128 ac.Succeeded AccessibilityCheckSucceeded,
1129 l.FirstAuditTime
1130FROM
1131 TrialRequest tr INNER JOIN License l ON tr.LicenseID = l.LicenseID
1132 OUTER APPLY (SELECT TOP 1 * FROM LicenseRevocationCallback WHERE LicenseID = tr.LicenseID ORDER BY Timestamp) lrc
1133 OUTER APPLY (SELECT COUNT(DISTINCT LicenseID) PreviousLicenseCount FROM LicenseRevocationCallback WHERE InstanceCode = lrc.InstanceCode AND Timestamp < lrc.Timestamp) lrc2
1134 OUTER APPLY (SELECT TOP 1 * FROM AccessibilityCheck iac WHERE InstanceCode = lrc.InstanceCode AND tr.Timestamp BETWEEN '7/2/2012 12:00' AND (SELECT DATEADD(s, 10, MIN(Timestamp)) FROM AccessibilityCheck WHERE InstanceCode = iac.InstanceCode) ORDER BY Succeeded DESC) ac
1135
1136
1137GO
1138/****** Object: View [dbo].[UnrevokedUpgradedLicenses] Script Date: 5/16/2018 4:53:35 PM ******/
1139SET ANSI_NULLS ON
1140GO
1141SET QUOTED_IDENTIFIER ON
1142GO
1143CREATE VIEW [dbo].[UnrevokedUpgradedLicenses] AS
1144select
1145[order].OrderID,
1146[OrderLineItem].ItemCode UnrevokedLicenseID,
1147[order].Timestamp OrderTime,
1148ordertransaction.Timestamp FullfillmentTime
1149from [order]
1150inner join orderlineitem on [order].OrderID = OrderLineItem.orderid
1151left outer join LicenseRevocation on OrderLineItem.ItemCode = LicenseRevocation.LicenseID
1152left outer join OrderTransaction on [order].orderid = OrderTransaction.orderid
1153where
1154OrderLineItem.ItemType = 1 and
1155[order].status = 1
1156and EffectiveTime is null
1157
1158GO
1159SET ANSI_PADDING ON
1160
1161GO
1162/****** Object: Index [IX_EmailHistory] Script Date: 5/16/2018 4:53:35 PM ******/
1163CREATE NONCLUSTERED INDEX [IX_EmailHistory] ON [dbo].[EmailHistory]
1164(
1165 [Archived] ASC,
1166 [EmailID] ASC,
1167 [ToEmailAddress] ASC
1168)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1169GO
1170SET ANSI_PADDING ON
1171
1172GO
1173/****** Object: Index [IX_EmailSchedule] Script Date: 5/16/2018 4:53:35 PM ******/
1174CREATE NONCLUSTERED INDEX [IX_EmailSchedule] ON [dbo].[EmailSchedule]
1175(
1176 [ScheduleType] ASC
1177)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1178GO
1179SET ANSI_PADDING ON
1180
1181GO
1182/****** Object: Index [IX_EmailUnsubscribe_EmailAddress] Script Date: 5/16/2018 4:53:35 PM ******/
1183CREATE NONCLUSTERED INDEX [IX_EmailUnsubscribe_EmailAddress] ON [dbo].[EmailUnsubscribe]
1184(
1185 [EmailAddress] ASC
1186)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1187GO
1188/****** Object: Index [IX_License] Script Date: 5/16/2018 4:53:35 PM ******/
1189CREATE NONCLUSTERED INDEX [IX_License] ON [dbo].[License]
1190(
1191 [Timestamp] ASC
1192)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1193GO
1194/****** Object: Index [IX_License_2] Script Date: 5/16/2018 4:53:35 PM ******/
1195CREATE NONCLUSTERED INDEX [IX_License_2] ON [dbo].[License]
1196(
1197 [FirstAuditTime] ASC
1198)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1199GO
1200/****** Object: Index [IX_LicensePurchase] Script Date: 5/16/2018 4:53:35 PM ******/
1201CREATE NONCLUSTERED INDEX [IX_LicensePurchase] ON [dbo].[LicensePurchase]
1202(
1203 [Timestamp] ASC
1204)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1205GO
1206SET ANSI_PADDING ON
1207
1208GO
1209/****** Object: Index [IX_LicensePurchase_EmailAddress] Script Date: 5/16/2018 4:53:35 PM ******/
1210CREATE NONCLUSTERED INDEX [IX_LicensePurchase_EmailAddress] ON [dbo].[LicensePurchase]
1211(
1212 [EmailAddress] ASC
1213)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1214GO
1215SET ANSI_PADDING ON
1216
1217GO
1218/****** Object: Index [IX_LicenseRevocationCallback] Script Date: 5/16/2018 4:53:35 PM ******/
1219CREATE UNIQUE NONCLUSTERED INDEX [IX_LicenseRevocationCallback] ON [dbo].[LicenseRevocationCallback]
1220(
1221 [InstanceCode] ASC,
1222 [LicenseID] ASC,
1223 [Timestamp] ASC
1224)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1225GO
1226SET ANSI_PADDING ON
1227
1228GO
1229/****** Object: Index [IX_TrialRequest] Script Date: 5/16/2018 4:53:35 PM ******/
1230CREATE NONCLUSTERED INDEX [IX_TrialRequest] ON [dbo].[TrialRequest]
1231(
1232 [Timestamp] ASC,
1233 [EmailAddress] ASC
1234)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1235GO
1236SET ARITHABORT ON
1237SET CONCAT_NULL_YIELDS_NULL ON
1238SET QUOTED_IDENTIFIER ON
1239SET ANSI_NULLS ON
1240SET ANSI_PADDING ON
1241SET ANSI_WARNINGS ON
1242SET NUMERIC_ROUNDABORT OFF
1243
1244GO
1245/****** Object: Index [IX_VersionCallback] Script Date: 5/16/2018 4:53:35 PM ******/
1246CREATE NONCLUSTERED INDEX [IX_VersionCallback] ON [dbo].[VersionCallback]
1247(
1248 [Archived] ASC,
1249 [IPFirst3Octets] ASC,
1250 [LicenseID] ASC,
1251 [Timestamp] ASC
1252)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1253GO
1254ALTER TABLE [dbo].[AccessibilityCheck] ADD CONSTRAINT [DF_AccessibilityCheck_TimeTakenMilliseconds] DEFAULT ((0)) FOR [TimeTakenMilliseconds]
1255GO
1256ALTER TABLE [dbo].[Company] ADD CONSTRAINT [DF_Company_AccountManager] DEFAULT (suser_sname()) FOR [AccountManager]
1257GO
1258ALTER TABLE [dbo].[CompanyLead] ADD CONSTRAINT [DF_CompanyLead_Timestamp] DEFAULT (getdate()) FOR [Timestamp]
1259GO
1260ALTER TABLE [dbo].[CompanyNote] ADD CONSTRAINT [Timestamp_Default] DEFAULT (getdate()) FOR [Timestamp]
1261GO
1262ALTER TABLE [dbo].[CompanyServiceWork] ADD CONSTRAINT [DF_CompanyServiceWork_Timestamp] DEFAULT (getdate()) FOR [Timestamp]
1263GO
1264ALTER TABLE [dbo].[EmailHistory] ADD DEFAULT ((0)) FOR [Archived]
1265GO
1266ALTER TABLE [dbo].[ExtensionRevision] ADD DEFAULT ((0)) FOR [IsPremium]
1267GO
1268ALTER TABLE [dbo].[LinkBuildNote] ADD CONSTRAINT [DF_LinkBuildNote_Timestamp] DEFAULT (getdate()) FOR [Timestamp]
1269GO
1270ALTER TABLE [dbo].[LinkBuildNote] ADD CONSTRAINT [DF_LinkBuildNote_WhoAdded] DEFAULT (suser_sname()) FOR [WhoAdded]
1271GO
1272ALTER TABLE [dbo].[LinkBuildSite] ADD CONSTRAINT [DF_LinkBuildSite_WhoAdded] DEFAULT (suser_sname()) FOR [WhoAdded]
1273GO
1274ALTER TABLE [dbo].[LinkBuildURL] ADD CONSTRAINT [DF_LinkBuildURL_Timestamp] DEFAULT (getdate()) FOR [Timestamp]
1275GO
1276ALTER TABLE [dbo].[LinkBuildURL] ADD CONSTRAINT [DF_LinkBuildURL_WhoAdded] DEFAULT (suser_sname()) FOR [WhoAdded]
1277GO
1278ALTER TABLE [dbo].[Order] ADD DEFAULT ((0)) FOR [TaxAmount]
1279GO
1280ALTER TABLE [dbo].[TrialRequest] ADD CONSTRAINT [DF__TrialRequ__IsBog__49C3F6B7] DEFAULT ((0)) FOR [IsBogus]
1281GO
1282ALTER TABLE [dbo].[VersionCallback] ADD CONSTRAINT [DF_VersionCallback_Platform] DEFAULT ((0)) FOR [Platform]
1283GO
1284ALTER TABLE [dbo].[VersionCallback] ADD CONSTRAINT [DF_VersionCallback_Archived] DEFAULT ((0)) FOR [Archived]
1285GO
1286ALTER TABLE [dbo].[CompanyLead] WITH NOCHECK ADD CONSTRAINT [FK_CompanyLead_Company] FOREIGN KEY([CompanyID])
1287REFERENCES [dbo].[Company] ([CompanyID])
1288ON UPDATE CASCADE
1289ON DELETE CASCADE
1290GO
1291ALTER TABLE [dbo].[CompanyLead] CHECK CONSTRAINT [FK_CompanyLead_Company]
1292GO
1293ALTER TABLE [dbo].[CompanyLicenseSearch] WITH NOCHECK ADD CONSTRAINT [FK_CompanyLicenseSearch_Company1] FOREIGN KEY([CompanyID])
1294REFERENCES [dbo].[Company] ([CompanyID])
1295ON DELETE CASCADE
1296GO
1297ALTER TABLE [dbo].[CompanyLicenseSearch] CHECK CONSTRAINT [FK_CompanyLicenseSearch_Company1]
1298GO
1299ALTER TABLE [dbo].[CompanyNote] WITH NOCHECK ADD CONSTRAINT [FK_CompanyNote_Company1] FOREIGN KEY([CompanyID])
1300REFERENCES [dbo].[Company] ([CompanyID])
1301ON DELETE CASCADE
1302GO
1303ALTER TABLE [dbo].[CompanyNote] CHECK CONSTRAINT [FK_CompanyNote_Company1]
1304GO
1305ALTER TABLE [dbo].[CompanyServiceWork] WITH NOCHECK ADD CONSTRAINT [FK_CompanyServiceWork_Company] FOREIGN KEY([CompanyID])
1306REFERENCES [dbo].[Company] ([CompanyID])
1307ON UPDATE CASCADE
1308ON DELETE CASCADE
1309GO
1310ALTER TABLE [dbo].[CompanyServiceWork] CHECK CONSTRAINT [FK_CompanyServiceWork_Company]
1311GO
1312ALTER TABLE [dbo].[ExtensionCategoryExtension] WITH NOCHECK ADD CONSTRAINT [FK_ExtensionCategoryExtension_Extension] FOREIGN KEY([ExtensionID])
1313REFERENCES [dbo].[Extension] ([ExtensionID])
1314GO
1315ALTER TABLE [dbo].[ExtensionCategoryExtension] CHECK CONSTRAINT [FK_ExtensionCategoryExtension_Extension]
1316GO
1317ALTER TABLE [dbo].[ExtensionCategoryExtension] WITH NOCHECK ADD CONSTRAINT [FK_ExtensionCategoryExtension_ExtensionCategory] FOREIGN KEY([CategoryCode])
1318REFERENCES [dbo].[ExtensionCategory] ([CategoryCode])
1319GO
1320ALTER TABLE [dbo].[ExtensionCategoryExtension] CHECK CONSTRAINT [FK_ExtensionCategoryExtension_ExtensionCategory]
1321GO
1322ALTER TABLE [dbo].[ExtensionRevision] WITH NOCHECK ADD CONSTRAINT [FK_ExtensionRevision_Extension] FOREIGN KEY([ExtensionID])
1323REFERENCES [dbo].[Extension] ([ExtensionID])
1324GO
1325ALTER TABLE [dbo].[ExtensionRevision] CHECK CONSTRAINT [FK_ExtensionRevision_Extension]
1326GO
1327ALTER TABLE [dbo].[LicenseAudit] WITH NOCHECK ADD CONSTRAINT [FK_LicenseAudit_License] FOREIGN KEY([LicenseID])
1328REFERENCES [dbo].[License] ([LicenseID])
1329GO
1330ALTER TABLE [dbo].[LicenseAudit] NOCHECK CONSTRAINT [FK_LicenseAudit_License]
1331GO
1332ALTER TABLE [dbo].[LicensePurchase] WITH NOCHECK ADD CONSTRAINT [FK_LicensePurchase_License] FOREIGN KEY([LicenseID])
1333REFERENCES [dbo].[License] ([LicenseID])
1334GO
1335ALTER TABLE [dbo].[LicensePurchase] NOCHECK CONSTRAINT [FK_LicensePurchase_License]
1336GO
1337ALTER TABLE [dbo].[LicenseRevocation] WITH NOCHECK ADD CONSTRAINT [FK_LicenseRevocation_License] FOREIGN KEY([LicenseID])
1338REFERENCES [dbo].[License] ([LicenseID])
1339GO
1340ALTER TABLE [dbo].[LicenseRevocation] NOCHECK CONSTRAINT [FK_LicenseRevocation_License]
1341GO
1342ALTER TABLE [dbo].[LicenseRevocationCallback] WITH NOCHECK ADD CONSTRAINT [FK_LicenseRevocationCallback_License] FOREIGN KEY([LicenseID])
1343REFERENCES [dbo].[License] ([LicenseID])
1344GO
1345ALTER TABLE [dbo].[LicenseRevocationCallback] NOCHECK CONSTRAINT [FK_LicenseRevocationCallback_License]
1346GO
1347ALTER TABLE [dbo].[LinkBuildNote] WITH NOCHECK ADD CONSTRAINT [FK_LinkBuildNote_LinkBuildSite] FOREIGN KEY([LinkBuildSiteID])
1348REFERENCES [dbo].[LinkBuildSite] ([LinkBuildSiteID])
1349ON DELETE CASCADE
1350GO
1351ALTER TABLE [dbo].[LinkBuildNote] CHECK CONSTRAINT [FK_LinkBuildNote_LinkBuildSite]
1352GO
1353ALTER TABLE [dbo].[LinkBuildURL] WITH NOCHECK ADD CONSTRAINT [FK_LinkBuildURL_LinkBuildSite] FOREIGN KEY([LinkBuildSiteID])
1354REFERENCES [dbo].[LinkBuildSite] ([LinkBuildSiteID])
1355ON DELETE CASCADE
1356GO
1357ALTER TABLE [dbo].[LinkBuildURL] CHECK CONSTRAINT [FK_LinkBuildURL_LinkBuildSite]
1358GO
1359ALTER TABLE [dbo].[OrderLineItem] WITH NOCHECK ADD CONSTRAINT [FK__OrderLine__Order__44CA3770] FOREIGN KEY([OrderID])
1360REFERENCES [dbo].[Order] ([OrderID])
1361GO
1362ALTER TABLE [dbo].[OrderLineItem] CHECK CONSTRAINT [FK__OrderLine__Order__44CA3770]
1363GO
1364ALTER TABLE [dbo].[OrderTransaction] WITH NOCHECK ADD CONSTRAINT [FK__OrderTran__Order__57DD0BE4] FOREIGN KEY([OrderID])
1365REFERENCES [dbo].[Order] ([OrderID])
1366GO
1367ALTER TABLE [dbo].[OrderTransaction] CHECK CONSTRAINT [FK__OrderTran__Order__57DD0BE4]
1368GO
1369ALTER TABLE [dbo].[TrialRequest] WITH NOCHECK ADD CONSTRAINT [FK_TrialRequest_License] FOREIGN KEY([LicenseID])
1370REFERENCES [dbo].[License] ([LicenseID])
1371GO
1372ALTER TABLE [dbo].[TrialRequest] NOCHECK CONSTRAINT [FK_TrialRequest_License]
1373GO
1374ALTER TABLE [dbo].[VersionCallback] WITH NOCHECK ADD CONSTRAINT [FK_VersionCallback_License] FOREIGN KEY([LicenseID])
1375REFERENCES [dbo].[License] ([LicenseID])
1376GO
1377ALTER TABLE [dbo].[VersionCallback] NOCHECK CONSTRAINT [FK_VersionCallback_License]
1378GO
1379/****** Object: StoredProcedure [dbo].[ArchiveCallbacksFromVersion] Script Date: 5/16/2018 4:53:35 PM ******/
1380SET ANSI_NULLS ON
1381GO
1382SET QUOTED_IDENTIFIER ON
1383GO
1384CREATE PROCEDURE [dbo].[ArchiveCallbacksFromVersion] (@VersionStartsWith VARCHAR(50)) AS
1385UPDATE VersionCallback set archived = 1 where version like @VersionStartsWith + '.%'
1386
1387GO
1388/****** Object: StoredProcedure [dbo].[CreateCompany] Script Date: 5/16/2018 4:53:35 PM ******/
1389SET ANSI_NULLS ON
1390GO
1391SET QUOTED_IDENTIFIER ON
1392GO
1393CREATE PROCEDURE [dbo].[CreateCompany] @CompanyName VARCHAR(50), @LicenseSearchText VARCHAR(100), @EstimatedAmount MONEY, @Certainty INT, @FollowUpDate DATETIME AS
1394DECLARE @CompanyID INT
1395INSERT INTO Company (CompanyName, FollowUpDate, AccountManager) VALUES (@CompanyName, @FollowUpDate, SYSTEM_USER)
1396SELECT @CompanyID = @@IDENTITY
1397INSERT INTO CompanyLicenseSearch VALUES (@CompanyID, @LicenseSearchText)
1398INSERT INTO CompanyLead (CompanyID, EstimatedAmount, Certainty) VALUES (@CompanyID, @EstimatedAmount, @Certainty)
1399
1400INSERT INTO CompanyNote
1401SELECT @CompanyID, tr.Timestamp, tr.Note
1402FROM CompanyLicense cl INNER JOIN TrialRequest tr on cl.LicenseID = tr.LicenseID
1403WHERE cl.CompanyID = @CompanyID AND NOT tr.Note IS NULL AND LEN(tr.Note) > 0
1404
1405GO
1406/****** Object: StoredProcedure [dbo].[CreateNewsletter] Script Date: 5/16/2018 4:53:35 PM ******/
1407SET ANSI_NULLS ON
1408GO
1409SET QUOTED_IDENTIFIER ON
1410GO
1411CREATE PROCEDURE [dbo].[CreateNewsletter] @EmailIDYearMonthPart VARCHAR(MAX) AS
1412DECLARE @FromEmailAddress VARCHAR(MAX)
1413DECLARE @Subject VARCHAR(MAX)
1414DECLARE @HtmlBody VARCHAR(MAX)
1415
1416SELECT TOP 1 @FromEmailAddress = FromEmailAddress, @Subject = Subject, @HtmlBody = HtmlBody FROM EmailSchedule WHERE EmailID LIKE 'Newsletter%' ORDER BY EmailID DESC
1417
1418INSERT INTO EmailSchedule VALUES ('Newsletter' + @EmailIDYearMonthPart, @FromEmailAddress, @Subject, NULL, @HtmlBody, 'OneTime', 'webmaster@elsitech.com', NULL)
1419
1420PRINT ''
1421PRINT 'UPDATE EmailSchedule SET Subject = ''' + @Subject + ''', HtmlBody = '''
1422PRINT CAST(LTRIM(RTRIM(@HtmlBody)) AS TEXT)
1423PRINT ''' WHERE EmailID = ''Newsletter' + @EmailIDYearMonthPart + ''''
1424
1425GO
1426/****** Object: StoredProcedure [dbo].[GetAllTablesInformation] Script Date: 5/16/2018 4:53:35 PM ******/
1427SET ANSI_NULLS ON
1428GO
1429SET QUOTED_IDENTIFIER ON
1430GO
1431CREATE PROCEDURE [dbo].[GetAllTablesInformation] AS
1432SELECT
1433 t.NAME AS TableName,
1434 p.rows AS RowCounts,
1435 SUM(a.total_pages) * 8 AS TotalSpaceKB,
1436 SUM(a.used_pages) * 8 AS UsedSpaceKB,
1437 (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
1438FROM
1439 sys.tables t
1440INNER JOIN
1441 sys.indexes i ON t.OBJECT_ID = i.object_id
1442INNER JOIN
1443 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
1444INNER JOIN
1445 sys.allocation_units a ON p.partition_id = a.container_id
1446WHERE
1447 t.NAME NOT LIKE 'dt%'
1448 AND t.is_ms_shipped = 0
1449 AND i.OBJECT_ID > 255
1450GROUP BY
1451 t.Name, p.Rows
1452ORDER BY
1453 TotalSpaceKB DESC
1454
1455GO
1456/****** Object: StoredProcedure [dbo].[GetEmailNewsletterStatistics] Script Date: 5/16/2018 4:53:35 PM ******/
1457SET ANSI_NULLS ON
1458GO
1459SET QUOTED_IDENTIFIER ON
1460GO
1461
1462CREATE PROCEDURE [dbo].[GetEmailNewsletterStatistics] AS
1463
1464select CONVERT(DATETIME, CONVERT(VARCHAR(20), a.Timestamp, 101)), datepart(hh, a.timestamp) SendHour, COUNT(*) Sends, COUNT(ViewTimestamp) Views, CAST(COUNT(ViewTimestamp) as FLOAT) / COUNT(*) ViewRate, COUNT(b.Timestamp) Unsubscribes, CAST(COUNT(b.Timestamp) as FLOAT) / COUNT(*) UnsubscribeRate
1465from emailhistory a left outer join EmailUnsubscribe b on a.EmailID = b.EmailID and a.ToEmailAddress = b.EmailAddress inner join EmailSchedule c on a.EmailID = c.EmailID
1466where c.ScheduleType = 'OneTime'
1467group by CONVERT(DATETIME, CONVERT(VARCHAR(20), a.Timestamp, 101)), datepart(hh, a.timestamp)
1468order by CONVERT(DATETIME, CONVERT(VARCHAR(20), a.Timestamp, 101)), datepart(hh, a.timestamp)
1469
1470select a.EmailID, COUNT(*) Sends, COUNT(ViewTimestamp) Views, CAST(COUNT(ViewTimestamp) as FLOAT) / COUNT(*) ViewRate, COUNT(b.Timestamp) Unsubscribes, CAST(COUNT(b.Timestamp) as FLOAT) / COUNT(*) UnsubscribeRate
1471from emailhistory a left outer join EmailUnsubscribe b on a.EmailID = b.EmailID and a.ToEmailAddress = b.EmailAddress inner join EmailSchedule c on a.EmailID = c.EmailID
1472where c.ScheduleType = 'OneTime'
1473group by a.EmailID
1474order by a.EmailID
1475
1476select datepart(hh, a.timestamp) SendHour, COUNT(*) Sends, COUNT(ViewTimestamp) Views, CAST(COUNT(ViewTimestamp) as FLOAT) / COUNT(*) ViewRate, COUNT(b.Timestamp) Unsubscribes, CAST(COUNT(b.Timestamp) as FLOAT) / COUNT(*) UnsubscribeRate
1477from emailhistory a left outer join EmailUnsubscribe b on a.EmailID = b.EmailID and a.ToEmailAddress = b.EmailAddress inner join EmailSchedule c on a.EmailID = c.EmailID
1478where c.ScheduleType = 'OneTime'
1479group by datepart(hh, a.timestamp)
1480order by datepart(hh, a.timestamp)
1481
1482
1483
1484GO
1485/****** Object: StoredProcedure [dbo].[GetPhysicalSizeOfTables] Script Date: 5/16/2018 4:53:35 PM ******/
1486SET ANSI_NULLS ON
1487GO
1488SET QUOTED_IDENTIFIER ON
1489GO
1490create procedure [dbo].[GetPhysicalSizeOfTables] AS
1491
1492CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
1493 reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
1494 unused VARCHAR(18))
1495
1496EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '
1497
1498SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
1499 CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
1500FROM #RowCountsAndSizes
1501ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName
1502
1503DROP TABLE #RowCountsAndSizes
1504
1505GO
1506/****** Object: StoredProcedure [dbo].[GetScheduledEmails] Script Date: 5/16/2018 4:53:35 PM ******/
1507SET ANSI_NULLS ON
1508GO
1509SET QUOTED_IDENTIFIER ON
1510GO
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520CREATE PROCEDURE [dbo].[GetScheduledEmails] AS
1521
1522SELECT DISTINCT TOP 20
1523 EmailID,
1524 ToEmailAddress,
1525 FromEmailAddress,
1526 Subject,
1527 PlainBody,
1528 HtmlBody,
1529 EndDate
1530FROM
1531 AllScheduledEmail se
1532WHERE
1533(
1534 NOT EXISTS (SELECT * FROM EmailHistory WHERE Archived = 0 AND EmailID = se.EmailID AND ToEmailAddress = se.ToEmailAddress)
1535)
1536AND
1537(
1538 NOT EXISTS (SELECT * FROM EmailUnsubscribe WHERE EmailAddress = se.ToEmailAddress)
1539)
1540AND
1541(
1542 -- Sunday=1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, Saturday=7
1543 DATEPART(dw, GETDATE()) >= 3 AND DATEPART(dw, GETDATE()) <= 6 AND DATEPART(hh, GETDATE()) >= 10 AND DATEPART(hh, GETDATE()) <= 17
1544)
1545UNION ALL
1546(
1547SELECT
1548 EmailID,
1549 Param1,
1550 FromEmailAddress,
1551 Subject,
1552 PlainBody,
1553 HtmlBody,
1554 '3000-01-01' EndDate
1555FROM
1556 EmailSchedule
1557WHERE
1558 ScheduleType = 'Test'
1559)
1560ORDER BY EndDate
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580GO
1581/****** Object: StoredProcedure [dbo].[InsertRevocationsForUnrevokedUpgradedLicenses] Script Date: 5/16/2018 4:53:35 PM ******/
1582SET ANSI_NULLS ON
1583GO
1584SET QUOTED_IDENTIFIER ON
1585GO
1586CREATE PROCEDURE [dbo].[InsertRevocationsForUnrevokedUpgradedLicenses] AS
1587insert into licenserevocation
1588select UnrevokedLicenseID, 1, 0x00000000, getdate(), fullfillmentTime from UnrevokedUpgradedLicenses
1589
1590GO
1591/****** Object: StoredProcedure [dbo].[ReplaceForumString] Script Date: 5/16/2018 4:53:35 PM ******/
1592SET ANSI_NULLS ON
1593GO
1594SET QUOTED_IDENTIFIER ON
1595GO
1596CREATE PROCEDURE [dbo].[ReplaceForumString] @oldString VARCHAR(MAX), @newString VARCHAR(MAX) AS
1597
1598declare @tableName varchar(max), @columnName varchar(max), @sql varchar(max)
1599declare c1 cursor for select table_name, column_name from screenconnectforum.information_schema.columns where data_type like '%varchar%'
1600open c1
1601
1602fetch next from c1 into @tableName, @columnName
1603
1604while @@FETCH_STATUS = 0
1605begin
1606 set @sql = 'update screenconnectforum.dbo.' + @tableName + ' set ' + @columnName + ' = ''' + @newString + ''' where ' + @columnName + ' = ''' + @oldString + ''''
1607 print 'executing: ' + @sql
1608 exec(@sql)
1609
1610 fetch next from c1 into @tableName, @columnName
1611end
1612
1613close c1
1614deallocate c1
1615
1616GO
1617/****** Object: StoredProcedure [dbo].[ShrinkLog] Script Date: 5/16/2018 4:53:35 PM ******/
1618SET ANSI_NULLS ON
1619GO
1620SET QUOTED_IDENTIFIER ON
1621GO
1622CREATE PROCEDURE [dbo].[ShrinkLog] AS
1623dbcc shrinkfile(screenconnectsite_log, 1)
1624
1625GO
1626/****** Object: StoredProcedure [dbo].[SyncForumAdminsWithJake] Script Date: 5/16/2018 4:53:35 PM ******/
1627SET ANSI_NULLS ON
1628GO
1629SET QUOTED_IDENTIFIER ON
1630GO
1631CREATE PROCEDURE [dbo].[SyncForumAdminsWithJake] AS
1632update a
1633set
1634 signature = b.signature,
1635 avatarimage = b.avatarimage,
1636 avatarimagetype = b.avatarimagetype,
1637 languagefile = null,
1638 themefile = null,
1639 overridedefaultthemes = 0
1640from screenconnectforum.dbo.yaf_user a, screenconnectforum.dbo.yaf_user b
1641where b.name = 'Jake' and a.rankid = 1
1642
1643select * from screenconnectforum.dbo.yaf_user where rankid = 1
1644
1645GO
1646/****** Object: StoredProcedure [dbo].[TrimAndArchiveDatabase] Script Date: 5/16/2018 4:53:35 PM ******/
1647SET ANSI_NULLS ON
1648GO
1649SET QUOTED_IDENTIFIER ON
1650GO
1651
1652
1653
1654
1655CREATE PROCEDURE [dbo].[TrimAndArchiveDatabase] AS
1656
1657PRINT 'Deleting all license revocation callbacks where multiple in one day by IP and LicenseID.';
1658
1659WITH a AS
1660(
1661 SELECT ROW_NUMBER() OVER (PARTITION BY IP, LicenseID, YEAR(Timestamp), MONTH(Timestamp) ORDER BY Timestamp) Number
1662 FROM LicenseRevocationCallback
1663 WHERE Timestamp < GETDATE() - 60
1664)
1665DELETE FROM a WHERE Number > 1;
1666
1667WITH a AS
1668(
1669 SELECT ROW_NUMBER() OVER (PARTITION BY IP, LicenseID, YEAR(Timestamp), MONTH(Timestamp), DAY(Timestamp) ORDER BY Timestamp) Number
1670 FROM LicenseRevocationCallback
1671)
1672DELETE FROM a WHERE Number > 1;
1673
1674
1675
1676
1677
1678PRINT 'Deleting all version callbacks where multiple in one day by IP and LicenseID.';
1679
1680WITH a AS
1681(
1682 SELECT ROW_NUMBER() OVER (PARTITION BY IP, LicenseID, Version, YEAR(Timestamp), MONTH(Timestamp) ORDER BY Timestamp) Number
1683 FROM VersionCallback
1684 WHERE Timestamp < GETDATE() - 60
1685)
1686DELETE FROM a WHERE Number > 1;
1687
1688WITH a AS
1689(
1690 SELECT ROW_NUMBER() OVER (PARTITION BY IP, LicenseID, Version, YEAR(Timestamp), MONTH(Timestamp), DAY(Timestamp) ORDER BY Timestamp) Number
1691 FROM VersionCallback
1692)
1693DELETE FROM a WHERE Number > 1;
1694
1695
1696
1697
1698
1699PRINT 'Archiving irrelevant version callbacks.';
1700
1701WITH a AS
1702(
1703 SELECT ROW_NUMBER() OVER (PARTITION BY IP, LicenseID, Version ORDER BY Timestamp) Number, Archived
1704 FROM VersionCallback
1705)
1706UPDATE a SET Archived = 1 WHERE Number > 1;
1707
1708
1709
1710
1711PRINT 'Archiving old version callbacks.';
1712
1713UPDATE VersionCallback SET Archived = 1 WHERE Timestamp < GETDATE() - 180
1714
1715
1716
1717
1718
1719PRINT 'Deleting license revocations for licenses that have already expired.';
1720
1721UPDATE LicenseRevocation
1722SET EffectiveTime = NULL
1723WHERE Reason = 4 AND LicenseID IN (SELECT LicenseID FROM License WHERE ExpirationDate < GETDATE())
1724
1725
1726
1727
1728
1729PRINT 'Deleting invalid unsubscribes.';
1730
1731WITH a AS
1732(
1733 SELECT ROW_NUMBER() OVER (PARTITION BY EmailAddress ORDER BY Timestamp) Number
1734 FROM EmailUnsubscribe
1735)
1736DELETE FROM a WHERE Number > 1;
1737
1738DELETE FROM EmailUnsubscribe WHERE EmailAddress LIKE '%@screenconnect.com'
1739
1740
1741
1742
1743
1744PRINT 'Archiving history.'
1745
1746UPDATE EmailHistory SET Archived = 1 WHERE Timestamp < GETDATE() - 30
1747
1748
1749
1750GO
1751/****** Object: DdlTrigger [rds_deny_backups_trigger] Script Date: 5/16/2018 4:53:35 PM ******/
1752SET ANSI_NULLS ON
1753GO
1754SET QUOTED_IDENTIFIER ON
1755GO
1756CREATE TRIGGER [rds_deny_backups_trigger] ON DATABASE WITH EXECUTE AS 'dbo' FOR
1757 ADD_ROLE_MEMBER, GRANT_DATABASE AS BEGIN
1758 SET NOCOUNT ON;
1759 SET ANSI_PADDING ON;
1760
1761 DECLARE @data xml;
1762 DECLARE @user sysname;
1763 DECLARE @role sysname;
1764 DECLARE @type sysname;
1765 DECLARE @sql NVARCHAR(MAX);
1766 DECLARE @permissions TABLE(name sysname PRIMARY KEY);
1767
1768 SELECT @data = EVENTDATA();
1769 SELECT @type = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
1770
1771 IF @type = 'ADD_ROLE_MEMBER' BEGIN
1772 SELECT @user = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
1773 @role = @data.value('(/EVENT_INSTANCE/RoleName)[1]', 'sysname');
1774
1775 IF @role IN ('db_owner', 'db_backupoperator') BEGIN
1776 SELECT @sql = 'DENY BACKUP DATABASE, BACKUP LOG TO ' + QUOTENAME(@user);
1777 EXEC(@sql);
1778 END
1779 END ELSE IF @type = 'GRANT_DATABASE' BEGIN
1780 INSERT INTO @permissions(name)
1781 SELECT Permission.value('(text())[1]', 'sysname') FROM
1782 @data.nodes('/EVENT_INSTANCE/Permissions/Permission')
1783 AS DatabasePermissions(Permission);
1784
1785 IF EXISTS (SELECT * FROM @permissions WHERE name IN ('BACKUP DATABASE',
1786 'BACKUP LOG'))
1787 RAISERROR('Cannot grant backup database or backup log', 15, 1) WITH LOG;
1788 END
1789END
1790
1791
1792GO
1793ENABLE TRIGGER [rds_deny_backups_trigger] ON DATABASE
1794GO
1795EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Licensee'
1796GO
1797EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Licensee'
1798GO
1799EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Licensee'
1800GO
1801EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=3315 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Licensee'
1802GO
1803EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Licensee'
1804GO
1805EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Date'
1806GO
1807EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Date'
1808GO
1809EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Date'
1810GO
1811EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1215 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Date'
1812GO
1813EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Date'
1814GO
1815EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Analytics'
1816GO
1817EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Analytics'
1818GO
1819EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Analytics'
1820GO
1821EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Analytics'
1822GO
1823EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Analytics'
1824GO
1825EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FullName'
1826GO
1827EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FullName'
1828GO
1829EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FullName'
1830GO
1831EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2355 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FullName'
1832GO
1833EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FullName'
1834GO
1835EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
1836GO
1837EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
1838GO
1839EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
1840GO
1841EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2130 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
1842GO
1843EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
1844GO
1845EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FirstSession'
1846GO
1847EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FirstSession'
1848GO
1849EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FirstSession'
1850GO
1851EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1245 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FirstSession'
1852GO
1853EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'FirstSession'
1854GO
1855EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastSession'
1856GO
1857EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastSession'
1858GO
1859EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastSession'
1860GO
1861EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=1155 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastSession'
1862GO
1863EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastSession'
1864GO
1865EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'NumSession'
1866GO
1867EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'NumSession'
1868GO
1869EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'NumSession'
1870GO
1871EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=555 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'NumSession'
1872GO
1873EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'NumSession'
1874GO
1875EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastFollowUp'
1876GO
1877EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastFollowUp'
1878GO
1879EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastFollowUp'
1880GO
1881EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastFollowUp'
1882GO
1883EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'LastFollowUp'
1884GO
1885EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'IsBogus'
1886GO
1887EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'IsBogus'
1888GO
1889EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'IsBogus'
1890GO
1891EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=900 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'IsBogus'
1892GO
1893EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'IsBogus'
1894GO
1895EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Note'
1896GO
1897EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Note'
1898GO
1899EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Note'
1900GO
1901EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=5385 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Note'
1902GO
1903EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber', @level2type=N'COLUMN',@level2name=N'Note'
1904GO
1905EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1906GO
1907EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1908GO
1909EXEC sys.sp_addextendedproperty @name=N'MS_FilterOnLoad', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1910GO
1911EXEC sys.sp_addextendedproperty @name=N'MS_HideNewField', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1912GO
1913EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=N'[CompanylessTrialPhoneNumber].[Date]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1914GO
1915EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1916GO
1917EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOnLoad', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1918GO
1919EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1920GO
1921EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1922GO
1923EXEC sys.sp_addextendedproperty @name=N'MS_TotalsRow', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanylessTrialPhoneNumber'
1924GO
1925EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'CompanyID'
1926GO
1927EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'CompanyID'
1928GO
1929EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'CompanyID'
1930GO
1931EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'CompanyID'
1932GO
1933EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'CompanyID'
1934GO
1935EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'LicenseID'
1936GO
1937EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'LicenseID'
1938GO
1939EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'LicenseID'
1940GO
1941EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'LicenseID'
1942GO
1943EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'LicenseID'
1944GO
1945EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Type'
1946GO
1947EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Type'
1948GO
1949EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Type'
1950GO
1951EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Type'
1952GO
1953EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Type'
1954GO
1955EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Licensee'
1956GO
1957EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Licensee'
1958GO
1959EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Licensee'
1960GO
1961EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Licensee'
1962GO
1963EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Licensee'
1964GO
1965EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Version'
1966GO
1967EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Version'
1968GO
1969EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Version'
1970GO
1971EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Version'
1972GO
1973EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Version'
1974GO
1975EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'ExpirationDate'
1976GO
1977EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'ExpirationDate'
1978GO
1979EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'ExpirationDate'
1980GO
1981EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2325 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'ExpirationDate'
1982GO
1983EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'ExpirationDate'
1984GO
1985EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'InstanceCount'
1986GO
1987EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'InstanceCount'
1988GO
1989EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'InstanceCount'
1990GO
1991EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'InstanceCount'
1992GO
1993EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'InstanceCount'
1994GO
1995EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Timestamp'
1996GO
1997EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Timestamp'
1998GO
1999EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Timestamp'
2000GO
2001EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=2565 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Timestamp'
2002GO
2003EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'Timestamp'
2004GO
2005EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'AnalyticsSource'
2006GO
2007EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'AnalyticsSource'
2008GO
2009EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'AnalyticsSource'
2010GO
2011EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'AnalyticsSource'
2012GO
2013EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'AnalyticsSource'
2014GO
2015EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'FullName'
2016GO
2017EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'FullName'
2018GO
2019EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'FullName'
2020GO
2021EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'FullName'
2022GO
2023EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'FullName'
2024GO
2025EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
2026GO
2027EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
2028GO
2029EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
2030GO
2031EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
2032GO
2033EXEC sys.sp_addextendedproperty @name=N'MS_TextAlign', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense', @level2type=N'COLUMN',@level2name=N'PhoneNumber'
2034GO
2035EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2036GO
2037EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2038GO
2039EXEC sys.sp_addextendedproperty @name=N'MS_FilterOnLoad', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2040GO
2041EXEC sys.sp_addextendedproperty @name=N'MS_HideNewField', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2042GO
2043EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2044GO
2045EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2046GO
2047EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOnLoad', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2048GO
2049EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2050GO
2051EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2052GO
2053EXEC sys.sp_addextendedproperty @name=N'MS_TotalsRow', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CompanyLicense'
2054GO
2055USE [master]
2056GO
2057ALTER DATABASE [ScreenConnectSite] SET READ_WRITE
2058GO