1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
USE [@initial.catalog@]
PRINT 'Creating table GLAccounts'
CREATE TABLE [dbo].[GLAccounts] (
[AccountNo] [int] NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_GLAccounts] PRIMARY KEY CLUSTERED
(
[AccountNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
PRINT 'Creating table InvoiceLineItems'
CREATE TABLE [dbo].[InvoiceLineItems] (
[InvoiceID] [int] NOT NULL ,
[InvoiceSequence] [smallint] NOT NULL ,
[AccountNo] [int] NOT NULL ,
[Amount] [money] NOT NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_InvoiceLineItems] PRIMARY KEY CLUSTERED
(
[InvoiceID],
[InvoiceSequence]
) ON [PRIMARY]
) ON [PRIMARY]
GO
PRINT 'Creating table Invoices'
CREATE TABLE [dbo].[Invoices] (
[InvoiceID] [int] IDENTITY (1, 1) NOT NULL ,
[VendorID] [int] NOT NULL ,
[InvoiceNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InvoiceDate] [smalldatetime] NOT NULL ,
[InvoiceTotal] [money] NOT NULL ,
[PaymentTotal] [money] NOT NULL CONSTRAINT [DF_Invoices_PaymentTotal] DEFAULT ((0)),
[CreditTotal] [money] NOT NULL CONSTRAINT [DF_Invoices_CreditTotal] DEFAULT ((0)),
[TermsID] [int] NOT NULL CONSTRAINT [DF_Invoices_TermsID] DEFAULT ((3)),
[DueDate] [smalldatetime] NOT NULL ,
[PaymentDate] [smalldatetime] NULL ,
CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
(
[InvoiceID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
PRINT 'Creating table States'
CREATE TABLE [dbo].[States] (
[StateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StateName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstZipCode] [int] NOT NULL ,
[LastZipCode] [int] NOT NULL ,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[StateCode]
) ON [PRIMARY]
) ON [PRIMARY]
GO
PRINT 'Creating table Terms'
CREATE TABLE [dbo].[Terms] (
[TermsID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DueDays] [smallint] NOT NULL ,
CONSTRAINT [PK_Terms] PRIMARY KEY CLUSTERED
(
[TermsID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
PRINT 'Creating table Vendors'
CREATE TABLE [dbo].[Vendors] (
[VendorID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ZipCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactLName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactFName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultTermsID] [int] NOT NULL CONSTRAINT [DF_Vendors_DefaultTermsID] DEFAULT ((3)),
[DefaultAccountNo] [int] NOT NULL CONSTRAINT [DF_Vendors_DefaultAccountNo] DEFAULT ((570)),
CONSTRAINT [PK_Vendors] PRIMARY KEY CLUSTERED
(
[VendorID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
|
