· 5 years ago · Apr 18, 2020, 03:12 AM
1/*
2** Copyright Microsoft, Inc. 1994 - 2000
3** All Rights Reserved.
4*/
5
6SET NOCOUNT ON
7GO
8
9USE master
10GO
11if exists (select * from sysdatabases where name='Northwind')
12 drop database Northwind
13go
14
15DECLARE @device_directory NVARCHAR(520)
16SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
17FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1
18
19EXECUTE (N'CREATE DATABASE Northwind
20 ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + @device_directory + N'northwnd.mdf'')
21 LOG ON (NAME = N''Northwind_log'', FILENAME = N''' + @device_directory + N'northwnd.ldf'')')
22go
23
24alter database Northwind set recovery simple
25GO
26
27set quoted_identifier on
28GO
29
30/* Set DATEFORMAT so that the date strings are interpreted correctly regardless of
31 the default DATEFORMAT on the server.
32*/
33SET DATEFORMAT mdy
34GO
35use "Northwind"
36go
37if exists (select * from sysobjects where id = object_id('dbo.Employee Sales by Country') and sysstat & 0xf = 4)
38 drop procedure "dbo"."Employee Sales by Country"
39GO
40if exists (select * from sysobjects where id = object_id('dbo.Sales by Year') and sysstat & 0xf = 4)
41 drop procedure "dbo"."Sales by Year"
42GO
43if exists (select * from sysobjects where id = object_id('dbo.Ten Most Expensive Products') and sysstat & 0xf = 4)
44 drop procedure "dbo"."Ten Most Expensive Products"
45GO
46if exists (select * from sysobjects where id = object_id('dbo.Category Sales for 1997') and sysstat & 0xf = 2)
47 drop view "dbo"."Category Sales for 1997"
48GO
49if exists (select * from sysobjects where id = object_id('dbo.Sales by Category') and sysstat & 0xf = 2)
50 drop view "dbo"."Sales by Category"
51GO
52if exists (select * from sysobjects where id = object_id('dbo.Sales Totals by Amount') and sysstat & 0xf = 2)
53 drop view "dbo"."Sales Totals by Amount"
54GO
55if exists (select * from sysobjects where id = object_id('dbo.Summary of Sales by Quarter') and sysstat & 0xf = 2)
56 drop view "dbo"."Summary of Sales by Quarter"
57GO
58if exists (select * from sysobjects where id = object_id('dbo.Summary of Sales by Year') and sysstat & 0xf = 2)
59 drop view "dbo"."Summary of Sales by Year"
60GO
61if exists (select * from sysobjects where id = object_id('dbo.Invoices') and sysstat & 0xf = 2)
62 drop view "dbo"."Invoices"
63GO
64if exists (select * from sysobjects where id = object_id('dbo.Order Details Extended') and sysstat & 0xf = 2)
65 drop view "dbo"."Order Details Extended"
66GO
67if exists (select * from sysobjects where id = object_id('dbo.Order Subtotals') and sysstat & 0xf = 2)
68 drop view "dbo"."Order Subtotals"
69GO
70if exists (select * from sysobjects where id = object_id('dbo.Product Sales for 1997') and sysstat & 0xf = 2)
71 drop view "dbo"."Product Sales for 1997"
72GO
73if exists (select * from sysobjects where id = object_id('dbo.Alphabetical list of products') and sysstat & 0xf = 2)
74 drop view "dbo"."Alphabetical list of products"
75GO
76if exists (select * from sysobjects where id = object_id('dbo.Current Product List') and sysstat & 0xf = 2)
77 drop view "dbo"."Current Product List"
78GO
79if exists (select * from sysobjects where id = object_id('dbo.Orders Qry') and sysstat & 0xf = 2)
80 drop view "dbo"."Orders Qry"
81GO
82if exists (select * from sysobjects where id = object_id('dbo.Products Above Average Price') and sysstat & 0xf = 2)
83 drop view "dbo"."Products Above Average Price"
84GO
85if exists (select * from sysobjects where id = object_id('dbo.Products by Category') and sysstat & 0xf = 2)
86 drop view "dbo"."Products by Category"
87GO
88if exists (select * from sysobjects where id = object_id('dbo.Quarterly Orders') and sysstat & 0xf = 2)
89 drop view "dbo"."Quarterly Orders"
90GO
91if exists (select * from sysobjects where id = object_id('dbo.Customer and Suppliers by City') and sysstat & 0xf = 2)
92 drop view "dbo"."Customer and Suppliers by City"
93GO
94if exists (select * from sysobjects where id = object_id('dbo.Order Details') and sysstat & 0xf = 3)
95 drop table "dbo"."Order Details"
96GO
97if exists (select * from sysobjects where id = object_id('dbo.Orders') and sysstat & 0xf = 3)
98 drop table "dbo"."Orders"
99GO
100if exists (select * from sysobjects where id = object_id('dbo.Products') and sysstat & 0xf = 3)
101 drop table "dbo"."Products"
102GO
103if exists (select * from sysobjects where id = object_id('dbo.Categories') and sysstat & 0xf = 3)
104 drop table "dbo"."Categories"
105GO
106if exists (select * from sysobjects where id = object_id('dbo.Customers') and sysstat & 0xf = 3)
107 drop table "dbo"."Customers"
108GO
109if exists (select * from sysobjects where id = object_id('dbo.Shippers') and sysstat & 0xf = 3)
110 drop table "dbo"."Shippers"
111GO
112if exists (select * from sysobjects where id = object_id('dbo.Suppliers') and sysstat & 0xf = 3)
113 drop table "dbo"."Suppliers"
114GO
115if exists (select * from sysobjects where id = object_id('dbo.Employees') and sysstat & 0xf = 3)
116 drop table "dbo"."Employees"
117GO
118CREATE TABLE "Employees" (
119 "EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
120 "LastName" nvarchar (20) NOT NULL ,
121 "FirstName" nvarchar (10) NOT NULL ,
122 "Title" nvarchar (30) NULL ,
123 "TitleOfCourtesy" nvarchar (25) NULL ,
124 "BirthDate" "datetime" NULL ,
125 "HireDate" "datetime" NULL ,
126 "Address" nvarchar (60) NULL ,
127 "City" nvarchar (15) NULL ,
128 "Region" nvarchar (15) NULL ,
129 "PostalCode" nvarchar (10) NULL ,
130 "Country" nvarchar (15) NULL ,
131 "HomePhone" nvarchar (24) NULL ,
132 "Extension" nvarchar (4) NULL ,
133 "Photo" "image" NULL ,
134 "Notes" "ntext" NULL ,
135 "ReportsTo" "int" NULL ,
136 "PhotoPath" nvarchar (255) NULL ,
137 CONSTRAINT "PK_Employees" PRIMARY KEY CLUSTERED
138 (
139 "EmployeeID"
140 ),
141 CONSTRAINT "FK_Employees_Employees" FOREIGN KEY
142 (
143 "ReportsTo"
144 ) REFERENCES "dbo"."Employees" (
145 "EmployeeID"
146 ),
147 CONSTRAINT "CK_Birthdate" CHECK (BirthDate < getdate())
148)