· 8 years ago · Nov 26, 2017, 12:26 PM
1IF NOT EXISTS ( SELECT *
2 FROM sys.objects
3 WHERE object_id = OBJECT_ID(N'DMSPDAOrder')
4 AND type IN ( N'U' ) )
5
6 CREATE TABLE [dbo].[DMSPDAOrder](
7 [CompanyID] [INT] NOT NULL,
8 [BranchID] [INT] NOT NULL,
9 [OrderType] [NVARCHAR](2) NOT NULL,
10 [OrderNbr] [NVARCHAR](20) NOT NULL,
11 [OrderDate] [DATETIME] NOT NULL,
12 [Description] [NVARCHAR](255) NULL,
13 [LineCntr] [INT] NULL,
14 [CustomerID] [NVARCHAR](20) NOT NULL,
15 [CustomerLocation] [INT] NOT NULL,
16 [Route] [NVARCHAR](20) NULL,
17 [SalesmanID] [NVARCHAR](20) NULL,
18 [TotalQuantity] [INT] NULL DEFAULT ((0)),
19 [TotalSKUs] [INT] NULL,
20 [OrderAmount] [DECIMAL](18, 2) NULL DEFAULT ((0)),
21 [LineDiscount] [DECIMAL](18, 2) NULL DEFAULT ((0)),
22 [DocumentDiscount] [DECIMAL](18, 2) NULL DEFAULT ((0)),
23 [OrderTotal] [DECIMAL](18, 2) NULL,
24 [Status] [CHAR](1) NOT NULL,
25 [PaymentStatus] [CHAR](10) NOT NULL,
26 [ValidDate] [BIT] NULL,
27 [ValidDistance] [BIT] NULL,
28 [CreatedByID] [UNIQUEIDENTIFIER] NOT NULL,
29 [CreatedByScreenID] [CHAR](8) NOT NULL,
30 [CreatedDateTime] [DATETIME] NOT NULL,
31 [LastModifiedByID] [UNIQUEIDENTIFIER] NOT NULL,
32 [LastModifiedByScreenID] [CHAR](8) NOT NULL,
33 [LastModifiedDateTime] [DATETIME] NOT NULL,
34 [TStamp] [TIMESTAMP] NOT NULL,
35 [Message] [NVARCHAR](500) NULL,
36 CONSTRAINT [PK_DMSPDAOrder] PRIMARY KEY CLUSTERED
37 (
38 [CompanyID] ASC,
39 [BranchID] ASC,
40 [OrderType] ASC,
41 [OrderNbr] ASC
42 ))
43ELSE
44 BEGIN
45 IF NOT EXISTS ( SELECT *
46 FROM INFORMATION_SCHEMA.COLUMNS
47 WHERE TABLE_NAME = 'DMSPDAOrder'
48 AND COLUMN_NAME = 'OrderOfNewCustomer' )
49 ALTER TABLE dbo.DMSPDAOrder ADD OrderOfNewCustomer BIT NULL;
50 END
51GO