Northwind Schema Report

Table of Contents
 Summary 
 Tables 
 Views 
 Procedures 
 DataTypes 

Summary
Property	Value	 
Report created	31-Oct-2005 20:52	 
DBMS and version	Microsoft SQL Server  [ 08.00.0760 ]	 
Server	TOSHIBA2	 
Database name	Northwind	 
User/Login		 
Schema/Owner		 
Driver and version	SQLSRV32.DLL  [ 03.85.1117 ]	 

Tables, 13 items

Table: 'dbo.Categories', 4 fields
Field Name	Type and Size	Default	Nullable	 
CategoryID	int identity		NOT NULL	 
CategoryName	nvarchar(15)		NOT NULL	 
Description	ntext		NULL	 
Picture	image		NULL	 

Primary key for table 'dbo.Categories'
Primary Key Name	Fields	 
PK_Categories	CategoryID	 

Indexes for table 'dbo.Categories', 2 items
Index Name	Clustered	Unique	Fields	 
PK_Categories	Yes	Yes	CategoryID	 
CategoryName	No	No	CategoryName	 

Dependencies for table 'dbo.Categories', 5 items
Object Name	Type	 
dbo.Alphabetical list of products	view	 
dbo.Product Sales for 1997	view	 
dbo.Products by Category	view	 
dbo.Sales by Category	view	 
dbo.SalesByCategory	stored procedure	 

Table: 'dbo.CustomerCustomerDemo', 2 fields
Field Name	Type and Size	Default	Nullable	 
CustomerID	nchar(5)		NOT NULL	 
CustomerTypeID	nchar(10)		NOT NULL	 

Primary key for table 'dbo.CustomerCustomerDemo'
Primary Key Name	Fields	 
PK_CustomerCustomerDemo	CustomerID, CustomerTypeID	 

Foreign keys for table 'dbo.CustomerCustomerDemo', 2 items
Foreign	Primary	 
CustomerCustomerDemo.CustomerTypeID	CustomerDemographics.CustomerTypeID	 
CustomerCustomerDemo.CustomerID	Customers.CustomerID	 

Indexes for table 'dbo.CustomerCustomerDemo', 2 items
Index Name	Clustered	Unique	Fields	 
PK_CustomerCustomerDemo	No	Yes	CustomerID, CustomerTypeID	 

Table: 'dbo.CustomerDemographics', 2 fields
Field Name	Type and Size	Default	Nullable	 
CustomerTypeID	nchar(10)		NOT NULL	 
CustomerDesc	ntext		NULL	 

Primary key for table 'dbo.CustomerDemographics'
Primary Key Name	Fields	 
PK_CustomerDemographics	CustomerTypeID	 

Indexes for table 'dbo.CustomerDemographics', 1 item
Index Name	Clustered	Unique	Fields	 
PK_CustomerDemographics	No	Yes	CustomerTypeID	 

Table: 'dbo.Customers', 11 fields
Field Name	Type and Size	Default	Nullable	 
CustomerID	nchar(5)		NOT NULL	 
CompanyName	nvarchar(40)		NOT NULL	 
ContactName	nvarchar(30)		NULL	 
ContactTitle	nvarchar(30)		NULL	 
Address	nvarchar(60)		NULL	 
City	nvarchar(15)		NULL	 
Region	nvarchar(15)		NULL	 
PostalCode	nvarchar(10)		NULL	 
Country	nvarchar(15)		NULL	 
Phone	nvarchar(24)		NULL	 
Fax	nvarchar(24)		NULL	 

Primary key for table 'dbo.Customers'
Primary Key Name	Fields	 
PK_Customers	CustomerID	 

Indexes for table 'dbo.Customers', 5 items
Index Name	Clustered	Unique	Fields	 
PK_Customers	Yes	Yes	CustomerID	 
City	No	No	City	 
CompanyName	No	No	CompanyName	 
PostalCode	No	No	PostalCode	 
Region	No	No	Region	 

Dependencies for table 'dbo.Customers', 6 items
Object Name	Type	 
dbo.CustOrderHist	stored procedure	 
dbo.Customer and Suppliers by City	view	 
dbo.Invoices	view	 
dbo.Orders Qry	view	 
dbo.Quarterly Orders	view	 
dbo.Sales Totals by Amount	view	 

Table: 'dbo.EmployeeTerritories', 2 fields
Field Name	Type and Size	Default	Nullable	 
EmployeeID	int		NOT NULL	 
TerritoryID	nvarchar(20)		NOT NULL	 

Primary key for table 'dbo.EmployeeTerritories'
Primary Key Name	Fields	 
PK_EmployeeTerritories	EmployeeID, TerritoryID	 

Foreign keys for table 'dbo.EmployeeTerritories', 2 items
Foreign	Primary	 
EmployeeTerritories.EmployeeID	Employees.EmployeeID	 
EmployeeTerritories.TerritoryID	Territories.TerritoryID	 

Indexes for table 'dbo.EmployeeTerritories', 2 items
Index Name	Clustered	Unique	Fields	 
PK_EmployeeTerritories	No	Yes	EmployeeID, TerritoryID	 

Table: 'dbo.Employees', 18 fields
Field Name	Type and Size	Default	Nullable	 
EmployeeID	int identity		NOT NULL	 
LastName	nvarchar(20)		NOT NULL	 
FirstName	nvarchar(10)		NOT NULL	 
Title	nvarchar(30)		NULL	 
TitleOfCourtesy	nvarchar(25)		NULL	 
BirthDate	datetime		NULL	 
HireDate	datetime		NULL	 
Address	nvarchar(60)		NULL	 
City	nvarchar(15)		NULL	 
Region	nvarchar(15)		NULL	 
PostalCode	nvarchar(10)		NULL	 
Country	nvarchar(15)		NULL	 
HomePhone	nvarchar(24)		NULL	 
Extension	nvarchar(4)		NULL	 
Photo	image		NULL	 
Notes	ntext		NULL	 
ReportsTo	int		NULL	 
PhotoPath	nvarchar(255)		NULL	 

Primary key for table 'dbo.Employees'
Primary Key Name	Fields	 
PK_Employees	EmployeeID	 

Foreign keys for table 'dbo.Employees', 1 item
Foreign	Primary	 
Employees.ReportsTo	Employees.EmployeeID	 

Indexes for table 'dbo.Employees', 3 items
Index Name	Clustered	Unique	Fields	 
PK_Employees	Yes	Yes	EmployeeID	 
LastName	No	No	LastName	 
PostalCode	No	No	PostalCode	 

Dependencies for table 'dbo.Employees', 3 items
Object Name	Type	 
dbo.CK_Birthdate	check cns	 
dbo.Employee Sales by Country	stored procedure	 
dbo.Invoices	view	 

Table: 'dbo."Order Details"', 5 fields
Field Name	Type and Size	Default	Nullable	 
OrderID	int		NOT NULL	 
ProductID	int		NOT NULL	 
UnitPrice	money(19.4)	(0)	NOT NULL	 
Quantity	smallint	(1)	NOT NULL	 
Discount	real	(0)	NOT NULL	 

Primary key for table 'dbo."Order Details"'
Primary Key Name	Fields	 
PK_Order_Details	OrderID, ProductID	 

Foreign keys for table 'dbo."Order Details"', 2 items
Foreign	Primary	 
Order Details.OrderID	Orders.OrderID	 
Order Details.ProductID	Products.ProductID	 

Dependencies for table 'dbo."Order Details"', 10 items
Object Name	Type	 
dbo.CK_Discount	check cns	 
dbo.CK_Quantity	check cns	 
dbo.CK_UnitPrice	check cns	 
dbo.CustOrderHist	stored procedure	 
dbo.CustOrdersDetail	stored procedure	 
dbo.Invoices	view	 
dbo.Order Details Extended	view	 
dbo.Order Subtotals	view	 
dbo.Product Sales for 1997	view	 
dbo.SalesByCategory	stored procedure	 

Table: 'dbo.Orders', 14 fields
Field Name	Type and Size	Default	Nullable	 
OrderID	int identity		NOT NULL	 
CustomerID	nchar(5)		NULL	 
EmployeeID	int		NULL	 
OrderDate	datetime		NULL	 
RequiredDate	datetime		NULL	 
ShippedDate	datetime		NULL	 
ShipVia	int		NULL	 
Freight	money(19.4)	(0)	NULL	 
ShipName	nvarchar(40)		NULL	 
ShipAddress	nvarchar(60)		NULL	 
ShipCity	nvarchar(15)		NULL	 
ShipRegion	nvarchar(15)		NULL	 
ShipPostalCode	nvarchar(10)		NULL	 
ShipCountry	nvarchar(15)		NULL	 

Primary key for table 'dbo.Orders'
Primary Key Name	Fields	 
PK_Orders	OrderID	 

Foreign keys for table 'dbo.Orders', 3 items
Foreign	Primary	 
Orders.CustomerID	Customers.CustomerID	 
Orders.EmployeeID	Employees.EmployeeID	 
Orders.ShipVia	Shippers.ShipperID	 

Indexes for table 'dbo.Orders', 9 items
Index Name	Clustered	Unique	Fields	 
PK_Orders	Yes	Yes	OrderID	 
CustomerID	No	No	CustomerID	 
CustomersOrders	No	No	CustomerID	 
EmployeeID	No	No	EmployeeID	 
EmployeesOrders	No	No	EmployeeID	 
OrderDate	No	No	OrderDate	 
ShipPostalCode	No	No	ShipPostalCode	 
ShippedDate	No	No	ShippedDate	 
ShippersOrders	No	No	ShipVia	 

Dependencies for table 'dbo.Orders', 13 items
Object Name	Type	 
dbo.CustOrderHist	stored procedure	 
dbo.CustOrdersOrders	stored procedure	 
dbo.Employee Sales by Country	stored procedure	 
dbo.Invoices	view	 
dbo.Orders Qry	view	 
dbo.Product Sales for 1997	view	 
dbo.Quarterly Orders	view	 
dbo.Sales Totals by Amount	view	 
dbo.Sales by Category	view	 
dbo.Sales by Year	stored procedure	 
dbo.SalesByCategory	stored procedure	 
dbo.Summary of Sales by Quarter	view	 
dbo.Summary of Sales by Year	view	 

Table: 'dbo.Products', 10 fields
Field Name	Type and Size	Default	Nullable	 
ProductID	int identity		NOT NULL	 
ProductName	nvarchar(40)		NOT NULL	 
SupplierID	int		NULL	 
CategoryID	int		NULL	 
QuantityPerUnit	nvarchar(20)		NULL	 
UnitPrice	money(19.4)	(0)	NULL	 
UnitsInStock	smallint	(0)	NULL	 
UnitsOnOrder	smallint	(0)	NULL	 
ReorderLevel	smallint	(0)	NULL	 
Discontinued	bit	(0)	NOT NULL	 

Primary key for table 'dbo.Products'
Primary Key Name	Fields	 
PK_Products	ProductID	 

Foreign keys for table 'dbo.Products', 2 items
Foreign	Primary	 
Products.CategoryID	Categories.CategoryID	 
Products.SupplierID	Suppliers.SupplierID	 

Indexes for table 'dbo.Products', 6 items
Index Name	Clustered	Unique	Fields	 
PK_Products	Yes	Yes	ProductID	 
CategoriesProducts	No	No	CategoryID	 
CategoryID	No	No	CategoryID	 
ProductName	No	No	ProductName	 
SupplierID	No	No	SupplierID	 
SuppliersProducts	No	No	SupplierID	 

Dependencies for table 'dbo.Products', 16 items
Object Name	Type	 
dbo.Alphabetical list of products	view	 
dbo.CK_Products_UnitPrice	check cns	 
dbo.CK_ReorderLevel	check cns	 
dbo.CK_UnitsInStock	check cns	 
dbo.CK_UnitsOnOrder	check cns	 
dbo.Current Product List	view	 
dbo.CustOrderHist	stored procedure	 
dbo.CustOrdersDetail	stored procedure	 
dbo.Invoices	view	 
dbo.Order Details Extended	view	 
dbo.Product Sales for 1997	view	 
dbo.Products Above Average Price	view	 
dbo.Products by Category	view	 
dbo.Sales by Category	view	 
dbo.SalesByCategory	stored procedure	 
dbo.Ten Most Expensive Products	stored procedure	 

Table: 'dbo.Region', 2 fields
Field Name	Type and Size	Default	Nullable	 
RegionID	int		NOT NULL	 
RegionDescription	nchar(50)		NOT NULL	 

Primary key for table 'dbo.Region'
Primary Key Name	Fields	 
PK_Region	RegionID	 

Indexes for table 'dbo.Region', 1 item
Index Name	Clustered	Unique	Fields	 
PK_Region	No	Yes	RegionID	 

Table: 'dbo.Shippers', 3 fields
Field Name	Type and Size	Default	Nullable	 
ShipperID	int identity		NOT NULL	 
CompanyName	nvarchar(40)		NOT NULL	 
Phone	nvarchar(24)		NULL	 

Primary key for table 'dbo.Shippers'
Primary Key Name	Fields	 
PK_Shippers	ShipperID	 

Indexes for table 'dbo.Shippers', 1 item
Index Name	Clustered	Unique	Fields	 
PK_Shippers	Yes	Yes	ShipperID	 

Dependencies for table 'dbo.Shippers', 1 item
Object Name	Type	 
dbo.Invoices	view	 

Table: 'dbo.Suppliers', 12 fields
Field Name	Type and Size	Default	Nullable	 
SupplierID	int identity		NOT NULL	 
CompanyName	nvarchar(40)		NOT NULL	 
ContactName	nvarchar(30)		NULL	 
ContactTitle	nvarchar(30)		NULL	 
Address	nvarchar(60)		NULL	 
City	nvarchar(15)		NULL	 
Region	nvarchar(15)		NULL	 
PostalCode	nvarchar(10)		NULL	 
Country	nvarchar(15)		NULL	 
Phone	nvarchar(24)		NULL	 
Fax	nvarchar(24)		NULL	 
HomePage	ntext		NULL	 

Primary key for table 'dbo.Suppliers'
Primary Key Name	Fields	 
PK_Suppliers	SupplierID	 

Indexes for table 'dbo.Suppliers', 3 items
Index Name	Clustered	Unique	Fields	 
PK_Suppliers	Yes	Yes	SupplierID	 
CompanyName	No	No	CompanyName	 
PostalCode	No	No	PostalCode	 

Dependencies for table 'dbo.Suppliers', 1 item
Object Name	Type	 
dbo.Customer and Suppliers by City	view	 

Table: 'dbo.Territories', 3 fields
Field Name	Type and Size	Default	Nullable	 
TerritoryID	nvarchar(20)		NOT NULL	 
TerritoryDescription	nchar(50)		NOT NULL	 
RegionID	int		NOT NULL	 

Primary key for table 'dbo.Territories'
Primary Key Name	Fields	 
PK_Territories	TerritoryID	 

Foreign keys for table 'dbo.Territories', 1 item
Foreign	Primary	 
Territories.RegionID	Region.RegionID	 

Indexes for table 'dbo.Territories', 1 item
Index Name	Clustered	Unique	Fields	 
PK_Territories	No	Yes	TerritoryID	 

Views, 16 items

View: 'dbo."Alphabetical list of products"'
Column	Type and Size	 
ProductID	int NOT NULL	 
ProductName	nvarchar(40) NOT NULL	 
SupplierID	int NULL	 
CategoryID	int NULL	 
QuantityPerUnit	nvarchar(20) NULL	 
UnitPrice	money(19.4) NULL	 
UnitsInStock	smallint NULL	 
UnitsOnOrder	smallint NULL	 
ReorderLevel	smallint NULL	 
Discontinued	bit NOT NULL	 
CategoryName	nvarchar(15) NOT NULL	 

View: 'dbo."Category Sales for 1997"'
Column	Type and Size	 
CategoryName	nvarchar(15) NOT NULL	 
CategorySales	money(19.4) NULL	 

View: 'dbo."Current Product List"'
Column	Type and Size	 
ProductID	int identity NOT NULL	 
ProductName	nvarchar(40) NOT NULL	 

View: 'dbo."Customer and Suppliers by City"'
Column	Type and Size	 
City	nvarchar(15) NULL	 
CompanyName	nvarchar(40) NOT NULL	 
ContactName	nvarchar(30) NULL	 
Relationship	varchar(9) NOT NULL	 

View: 'dbo.Invoices'
Column	Type and Size	 
ShipName	nvarchar(40) NULL	 
ShipAddress	nvarchar(60) NULL	 
ShipCity	nvarchar(15) NULL	 
ShipRegion	nvarchar(15) NULL	 
ShipPostalCode	nvarchar(10) NULL	 
ShipCountry	nvarchar(15) NULL	 
CustomerID	nchar(5) NULL	 
CustomerName	nvarchar(40) NOT NULL	 
Address	nvarchar(60) NULL	 
City	nvarchar(15) NULL	 
Region	nvarchar(15) NULL	 
PostalCode	nvarchar(10) NULL	 
Country	nvarchar(15) NULL	 
Salesperson	nvarchar(31) NOT NULL	 
OrderID	int NOT NULL	 
OrderDate	datetime NULL	 
RequiredDate	datetime NULL	 
ShippedDate	datetime NULL	 
ShipperName	nvarchar(40) NOT NULL	 
ProductID	int NOT NULL	 
ProductName	nvarchar(40) NOT NULL	 
UnitPrice	money(19.4) NOT NULL	 
Quantity	smallint NOT NULL	 
Discount	real NOT NULL	 
ExtendedPrice	money(19.4) NULL	 
Freight	money(19.4) NULL	 

View: 'dbo."Order Details Extended"'
Column	Type and Size	 
OrderID	int NOT NULL	 
ProductID	int NOT NULL	 
ProductName	nvarchar(40) NOT NULL	 
UnitPrice	money(19.4) NOT NULL	 
Quantity	smallint NOT NULL	 
Discount	real NOT NULL	 
ExtendedPrice	money(19.4) NULL	 

View: 'dbo."Order Subtotals"'
Column	Type and Size	 
OrderID	int NOT NULL	 
Subtotal	money(19.4) NULL	 

View: 'dbo."Orders Qry"'
Column	Type and Size	 
OrderID	int NOT NULL	 
CustomerID	nchar(5) NULL	 
EmployeeID	int NULL	 
OrderDate	datetime NULL	 
RequiredDate	datetime NULL	 
ShippedDate	datetime NULL	 
ShipVia	int NULL	 
Freight	money(19.4) NULL	 
ShipName	nvarchar(40) NULL	 
ShipAddress	nvarchar(60) NULL	 
ShipCity	nvarchar(15) NULL	 
ShipRegion	nvarchar(15) NULL	 
ShipPostalCode	nvarchar(10) NULL	 
ShipCountry	nvarchar(15) NULL	 
CompanyName	nvarchar(40) NOT NULL	 
Address	nvarchar(60) NULL	 
City	nvarchar(15) NULL	 
Region	nvarchar(15) NULL	 
PostalCode	nvarchar(10) NULL	 
Country	nvarchar(15) NULL	 

View: 'dbo."Product Sales for 1997"'
Column	Type and Size	 
CategoryName	nvarchar(15) NOT NULL	 
ProductName	nvarchar(40) NOT NULL	 
ProductSales	money(19.4) NULL	 

View: 'dbo."Products Above Average Price"'
Column	Type and Size	 
ProductName	nvarchar(40) NOT NULL	 
UnitPrice	money(19.4) NULL	 

View: 'dbo."Products by Category"'
Column	Type and Size	 
CategoryName	nvarchar(15) NOT NULL	 
ProductName	nvarchar(40) NOT NULL	 
QuantityPerUnit	nvarchar(20) NULL	 
UnitsInStock	smallint NULL	 
Discontinued	bit NOT NULL	 

View: 'dbo."Quarterly Orders"'
Column	Type and Size	 
CustomerID	nchar(5) NULL	 
CompanyName	nvarchar(40) NULL	 
City	nvarchar(15) NULL	 
Country	nvarchar(15) NULL	 

View: 'dbo."Sales Totals by Amount"'
Column	Type and Size	 
SaleAmount	money(19.4) NULL	 
OrderID	int NOT NULL	 
CompanyName	nvarchar(40) NOT NULL	 
ShippedDate	datetime NULL	 

View: 'dbo."Sales by Category"'
Column	Type and Size	 
CategoryID	int NOT NULL	 
CategoryName	nvarchar(15) NOT NULL	 
ProductName	nvarchar(40) NOT NULL	 
ProductSales	money(19.4) NULL	 

View: 'dbo."Summary of Sales by Quarter"'
Column	Type and Size	 
ShippedDate	datetime NULL	 
OrderID	int NOT NULL	 
Subtotal	money(19.4) NULL	 

View: 'dbo."Summary of Sales by Year"'
Column	Type and Size	 
ShippedDate	datetime NULL	 
OrderID	int NOT NULL	 
Subtotal	money(19.4) NULL	 

Procedures, 8 items
Procedure Name	Note	Parameters	 
dbo.CustOrderHist		@RETURN_VALUE int(10)  [RETURN VALUE],@CustomerID nchar(5)  [INPUT]	 
dbo.CustOrdersDetail		@RETURN_VALUE int(10)  [RETURN VALUE],@OrderID int(10)  [INPUT]	 
dbo.CustOrdersOrders		@RETURN_VALUE int(10)  [RETURN VALUE],@CustomerID nchar(5)  [INPUT]	 
dbo."Employee Sales by Country"		@RETURN_VALUE int(10)  [RETURN VALUE],@Beginning_Date datetime(23)  [INPUT],@Ending_Date datetime(23)  [INPUT]	 
dbo."Sales by Year"		@RETURN_VALUE int(10)  [RETURN VALUE],@Beginning_Date datetime(23)  [INPUT],@Ending_Date datetime(23)  [INPUT]	 
dbo.SalesByCategory		@RETURN_VALUE int(10)  [RETURN VALUE],@CategoryName nvarchar(15)  [INPUT],@OrdYear nvarchar(4)  [INPUT]	 
dbo."Ten Most Expensive Products"		@RETURN_VALUE int(10)  [RETURN VALUE]	 
dbo.TestSalesByStore		@RETURN_VALUE int(10)  [RETURN VALUE]	 

Data Types, 32 items
Type name	Code	Size	Nullable	Searchable	Scale	 
sql_variant	-150	8000	YES	except LIKE		 
uniqueidentifier	-11	36	YES	except LIKE		 
ntext	-10	1073741823	YES	LIKE ONLY		 
nvarchar	-9	4000	YES	YES		 
sysname	-9	128	NO	YES		 
nchar	-8	4000	YES	YES		 
bit	-7	1	YES	except LIKE		 
tinyint	-6	3	YES	except LIKE		 
tinyint identity	-6	3	NO	except LIKE		 
bigint	-5	19	YES	except LIKE		 
bigint identity	-5	19	NO	except LIKE		 
image	-4	2147483647	YES	NO		 
varbinary	-3	8000	YES	except LIKE		 
binary	-2	8000	YES	except LIKE		 
timestamp	-2	8	NO	except LIKE		 
text	-1	2147483647	YES	LIKE ONLY		 
char	1	8000	YES	YES		 
numeric	2	38	YES	except LIKE	0..38	 
numeric() identity	2	38	NO	except LIKE		 
decimal	3	38	YES	except LIKE	0..38	 
money	3	19	YES	except LIKE		 
smallmoney	3	10	YES	except LIKE		 
decimal() identity	3	38	NO	except LIKE		 
int	4	10	YES	except LIKE		 
int identity	4	10	NO	except LIKE		 
smallint	5	5	YES	except LIKE		 
smallint identity	5	5	NO	except LIKE		 
float	6	15	YES	except LIKE		 
real	7	7	YES	except LIKE		 
datetime	11	23	YES	YES		 
smalldatetime	11	16	YES	YES		 
varchar	12	8000	YES	YES