Northwind Schema Report

Table of Contents

• Summary
• Tables
• Views
• Procedures
• DataTypes

Property	Value
Report created	31-Oct-2005 21:22
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 (34 bytes)

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 (30 bytes)

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 (20 bytes)

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 (536 bytes)

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 (44 bytes)

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 (1006 bytes)

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 (35 bytes)

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 (401 bytes)

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 (160 bytes)

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 (104 bytes)

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 (132 bytes)

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 (530 bytes)

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 (144 bytes)

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