· 8 years ago · Jun 03, 2018, 12:22 PM
1USE master;
2
3IF EXISTS(SELECT * FROM sys.databases where name = 'IndexesTestDB')
4 DROP DATABASE IndexesTestDB;
5GO
6CREATE DATABASE IndexesTestDB;
7GO
8USE IndexesTestDB;
9GO
10
11if exists (select 1
12 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
13 where r.fkeyid = object_id('"Order"') and o.name = 'FK_ORDER_REFERENCE_CUSTOMER')
14alter table "Order"
15 drop constraint FK_ORDER_REFERENCE_CUSTOMER
16go
17
18if exists (select 1
19 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
20 where r.fkeyid = object_id('OrderItem') and o.name = 'FK_ORDERITE_REFERENCE_ORDER')
21alter table OrderItem
22 drop constraint FK_ORDERITE_REFERENCE_ORDER
23go
24
25if exists (select 1
26 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
27 where r.fkeyid = object_id('OrderItem') and o.name = 'FK_ORDERITE_REFERENCE_PRODUCT')
28alter table OrderItem
29 drop constraint FK_ORDERITE_REFERENCE_PRODUCT
30go
31
32if exists (select 1
33 from sysobjects
34 where id = object_id('Customer')
35 and type = 'U')
36 drop table Customer
37go
38
39if exists (select 1
40 from sysobjects
41 where id = object_id('"Order"')
42 and type = 'U')
43 drop table "Order"
44go
45
46if exists (select 1
47 from sysobjects
48 where id = object_id('OrderItem')
49 and type = 'U')
50 drop table OrderItem
51go
52
53if exists (select 1
54 from sysobjects
55 where id = object_id('Product')
56 and type = 'U')
57 drop table Product
58go
59
60if exists (select 1
61 from sysobjects
62 where id = object_id('Supplier')
63 and type = 'U')
64 drop table Supplier
65go
66
67/*==============================================================*/
68/* Table: Customer */
69/*==============================================================*/
70create table Customer (
71 Id int identity,
72 FirstName nvarchar(40) not null,
73 LastName nvarchar(40) not null,
74 City nvarchar(40) null,
75 Country nvarchar(40) null,
76 Phone nvarchar(20) null
77 )
78go
79
80/*==============================================================*/
81/* Table: "Order" */
82/*==============================================================*/
83create table "Order" (
84 Id int identity,
85 OrderDate datetime not null default getdate(),
86 OrderNumber nvarchar(10) null,
87 CustomerId int not null,
88 TotalAmount decimal(12,2) null default 0
89)
90go
91
92/*==============================================================*/
93/* Table: OrderItem */
94/*==============================================================*/
95create table OrderItem (
96 Id int identity,
97 OrderId int not null,
98 ProductId int not null,
99 UnitPrice decimal(12,2) not null default 0,
100 Quantity int not null default 1
101)
102go
103
104/*==============================================================*/
105/* Table: Product */
106/*==============================================================*/
107create table Product (
108 Id int identity,
109 ProductName nvarchar(50) not null,
110 SupplierId int not null,
111 UnitPrice decimal(12,2) null default 0,
112 Package nvarchar(30) null,
113 IsDiscontinued bit not null default 0
114)
115go
116
117
118/*==============================================================*/
119/* Table: Supplier */
120/*==============================================================*/
121create table Supplier (
122 Id int identity,
123 CompanyName nvarchar(40) not null,
124 ContactName nvarchar(50) null,
125 ContactTitle nvarchar(40) null,
126 City nvarchar(40) null,
127 Country nvarchar(40) null,
128 Phone nvarchar(30) null,
129 Fax nvarchar(30) null
130)
131go