Northwind Schema Report

Table of Contents

Summary

PropertyValue
Report created31-Oct-2005 21:22
DBMS and versionMicrosoft SQL Server [ 08.00.0760 ]
ServerTOSHIBA2
Database nameNorthwind
User/Login 
Schema/Owner 
Driver and versionSQLSRV32.DLL [ 03.85.1117 ]

Tables, 13 items

Table: 'dbo.Categories', 4 fields (34 bytes)

Field NameType and SizeDefaultNullable
CategoryIDint identity NOT NULL
CategoryNamenvarchar(15) NOT NULL
Descriptionntext NULL
Pictureimage NULL

Primary key for table 'dbo.Categories'

Primary Key NameFields
PK_CategoriesCategoryID

Indexes for table 'dbo.Categories', 2 items

Index NameClusteredUniqueFields
PK_CategoriesYesYesCategoryID
CategoryNameNoNoCategoryName

Dependencies for table 'dbo.Categories', 5 items

Object NameType
dbo.Alphabetical list of productsview
dbo.Product Sales for 1997view
dbo.Products by Categoryview
dbo.Sales by Categoryview
dbo.SalesByCategorystored procedure

Table: 'dbo.CustomerCustomerDemo', 2 fields (30 bytes)

Field NameType and SizeDefaultNullable
CustomerIDnchar(5) NOT NULL
CustomerTypeIDnchar(10) NOT NULL

Primary key for table 'dbo.CustomerCustomerDemo'

Primary Key NameFields
PK_CustomerCustomerDemoCustomerID, CustomerTypeID

Foreign keys for table 'dbo.CustomerCustomerDemo', 2 items

ForeignPrimary
CustomerCustomerDemo.CustomerTypeIDCustomerDemographics.CustomerTypeID
CustomerCustomerDemo.CustomerIDCustomers.CustomerID

Indexes for table 'dbo.CustomerCustomerDemo', 2 items

Index NameClusteredUniqueFields
PK_CustomerCustomerDemoNoYesCustomerID, CustomerTypeID

Table: 'dbo.CustomerDemographics', 2 fields (20 bytes)

Field NameType and SizeDefaultNullable
CustomerTypeIDnchar(10) NOT NULL
CustomerDescntext NULL

Primary key for table 'dbo.CustomerDemographics'

Primary Key NameFields
PK_CustomerDemographicsCustomerTypeID

Indexes for table 'dbo.CustomerDemographics', 1 item

Index NameClusteredUniqueFields
PK_CustomerDemographicsNoYesCustomerTypeID

Table: 'dbo.Customers', 11 fields (536 bytes)

Field NameType and SizeDefaultNullable
CustomerIDnchar(5) NOT NULL
CompanyNamenvarchar(40) NOT NULL
ContactNamenvarchar(30) NULL
ContactTitlenvarchar(30) NULL
Addressnvarchar(60) NULL
Citynvarchar(15) NULL
Regionnvarchar(15) NULL
PostalCodenvarchar(10) NULL
Countrynvarchar(15) NULL
Phonenvarchar(24) NULL
Faxnvarchar(24) NULL

Primary key for table 'dbo.Customers'

Primary Key NameFields
PK_CustomersCustomerID

Indexes for table 'dbo.Customers', 5 items

Index NameClusteredUniqueFields
PK_CustomersYesYesCustomerID
CityNoNoCity
CompanyNameNoNoCompanyName
PostalCodeNoNoPostalCode
RegionNoNoRegion

Dependencies for table 'dbo.Customers', 6 items

Object NameType
dbo.CustOrderHiststored procedure
dbo.Customer and Suppliers by Cityview
dbo.Invoicesview
dbo.Orders Qryview
dbo.Quarterly Ordersview
dbo.Sales Totals by Amountview

Table: 'dbo.EmployeeTerritories', 2 fields (44 bytes)

Field NameType and SizeDefaultNullable
EmployeeIDint NOT NULL
TerritoryIDnvarchar(20) NOT NULL

Primary key for table 'dbo.EmployeeTerritories'

Primary Key NameFields
PK_EmployeeTerritoriesEmployeeID, TerritoryID

Foreign keys for table 'dbo.EmployeeTerritories', 2 items

ForeignPrimary
EmployeeTerritories.EmployeeIDEmployees.EmployeeID
EmployeeTerritories.TerritoryIDTerritories.TerritoryID

Indexes for table 'dbo.EmployeeTerritories', 2 items

Index NameClusteredUniqueFields
PK_EmployeeTerritoriesNoYesEmployeeID, TerritoryID

Table: 'dbo.Employees', 18 fields (1006 bytes)

Field NameType and SizeDefaultNullable
EmployeeIDint identity NOT NULL
LastNamenvarchar(20) NOT NULL
FirstNamenvarchar(10) NOT NULL
Titlenvarchar(30) NULL
TitleOfCourtesynvarchar(25) NULL
BirthDatedatetime NULL
HireDatedatetime NULL
Addressnvarchar(60) NULL
Citynvarchar(15) NULL
Regionnvarchar(15) NULL
PostalCodenvarchar(10) NULL
Countrynvarchar(15) NULL
HomePhonenvarchar(24) NULL
Extensionnvarchar(4) NULL
Photoimage NULL
Notesntext NULL
ReportsToint NULL
PhotoPathnvarchar(255) NULL

Primary key for table 'dbo.Employees'

Primary Key NameFields
PK_EmployeesEmployeeID

Foreign keys for table 'dbo.Employees', 1 item

ForeignPrimary
Employees.ReportsToEmployees.EmployeeID

Indexes for table 'dbo.Employees', 3 items

Index NameClusteredUniqueFields
PK_EmployeesYesYesEmployeeID
LastNameNoNoLastName
PostalCodeNoNoPostalCode

Dependencies for table 'dbo.Employees', 3 items

Object NameType
dbo.CK_Birthdatecheck cns
dbo.Employee Sales by Countrystored procedure
dbo.Invoicesview

Table: 'dbo."Order Details"', 5 fields (35 bytes)

Field NameType and SizeDefaultNullable
OrderIDint NOT NULL
ProductIDint NOT NULL
UnitPricemoney(19.4)(0)NOT NULL
Quantitysmallint(1)NOT NULL
Discountreal(0)NOT NULL

Primary key for table 'dbo."Order Details"'

Primary Key NameFields
PK_Order_DetailsOrderID, ProductID

Foreign keys for table 'dbo."Order Details"', 2 items

ForeignPrimary
Order Details.OrderIDOrders.OrderID
Order Details.ProductIDProducts.ProductID

Dependencies for table 'dbo."Order Details"', 10 items

Object NameType
dbo.CK_Discountcheck cns
dbo.CK_Quantitycheck cns
dbo.CK_UnitPricecheck cns
dbo.CustOrderHiststored procedure
dbo.CustOrdersDetailstored procedure
dbo.Invoicesview
dbo.Order Details Extendedview
dbo.Order Subtotalsview
dbo.Product Sales for 1997view
dbo.SalesByCategorystored procedure

Table: 'dbo.Orders', 14 fields (401 bytes)

Field NameType and SizeDefaultNullable
OrderIDint identity NOT NULL
CustomerIDnchar(5) NULL
EmployeeIDint NULL
OrderDatedatetime NULL
RequiredDatedatetime NULL
ShippedDatedatetime NULL
ShipViaint NULL
Freightmoney(19.4)(0)NULL
ShipNamenvarchar(40) NULL
ShipAddressnvarchar(60) NULL
ShipCitynvarchar(15) NULL
ShipRegionnvarchar(15) NULL
ShipPostalCodenvarchar(10) NULL
ShipCountrynvarchar(15) NULL

Primary key for table 'dbo.Orders'

Primary Key NameFields
PK_OrdersOrderID

Foreign keys for table 'dbo.Orders', 3 items

ForeignPrimary
Orders.CustomerIDCustomers.CustomerID
Orders.EmployeeIDEmployees.EmployeeID
Orders.ShipViaShippers.ShipperID

Indexes for table 'dbo.Orders', 9 items

Index NameClusteredUniqueFields
PK_OrdersYesYesOrderID
CustomerIDNoNoCustomerID
CustomersOrdersNoNoCustomerID
EmployeeIDNoNoEmployeeID
EmployeesOrdersNoNoEmployeeID
OrderDateNoNoOrderDate
ShipPostalCodeNoNoShipPostalCode
ShippedDateNoNoShippedDate
ShippersOrdersNoNoShipVia

Dependencies for table 'dbo.Orders', 13 items

Object NameType
dbo.CustOrderHiststored procedure
dbo.CustOrdersOrdersstored procedure
dbo.Employee Sales by Countrystored procedure
dbo.Invoicesview
dbo.Orders Qryview
dbo.Product Sales for 1997view
dbo.Quarterly Ordersview
dbo.Sales Totals by Amountview
dbo.Sales by Categoryview
dbo.Sales by Yearstored procedure
dbo.SalesByCategorystored procedure
dbo.Summary of Sales by Quarterview
dbo.Summary of Sales by Yearview

Table: 'dbo.Products', 10 fields (160 bytes)

Field NameType and SizeDefaultNullable
ProductIDint identity NOT NULL
ProductNamenvarchar(40) NOT NULL
SupplierIDint NULL
CategoryIDint NULL
QuantityPerUnitnvarchar(20) NULL
UnitPricemoney(19.4)(0)NULL
UnitsInStocksmallint(0)NULL
UnitsOnOrdersmallint(0)NULL
ReorderLevelsmallint(0)NULL
Discontinuedbit(0)NOT NULL

Primary key for table 'dbo.Products'

Primary Key NameFields
PK_ProductsProductID

Foreign keys for table 'dbo.Products', 2 items

ForeignPrimary
Products.CategoryIDCategories.CategoryID
Products.SupplierIDSuppliers.SupplierID

Indexes for table 'dbo.Products', 6 items

Index NameClusteredUniqueFields
PK_ProductsYesYesProductID
CategoriesProductsNoNoCategoryID
CategoryIDNoNoCategoryID
ProductNameNoNoProductName
SupplierIDNoNoSupplierID
SuppliersProductsNoNoSupplierID

Dependencies for table 'dbo.Products', 16 items

Object NameType
dbo.Alphabetical list of productsview
dbo.CK_Products_UnitPricecheck cns
dbo.CK_ReorderLevelcheck cns
dbo.CK_UnitsInStockcheck cns
dbo.CK_UnitsOnOrdercheck cns
dbo.Current Product Listview
dbo.CustOrderHiststored procedure
dbo.CustOrdersDetailstored procedure
dbo.Invoicesview
dbo.Order Details Extendedview
dbo.Product Sales for 1997view
dbo.Products Above Average Priceview
dbo.Products by Categoryview
dbo.Sales by Categoryview
dbo.SalesByCategorystored procedure
dbo.Ten Most Expensive Productsstored procedure

Table: 'dbo.Region', 2 fields (104 bytes)

Field NameType and SizeDefaultNullable
RegionIDint NOT NULL
RegionDescriptionnchar(50) NOT NULL

Primary key for table 'dbo.Region'

Primary Key NameFields
PK_RegionRegionID

Indexes for table 'dbo.Region', 1 item

Index NameClusteredUniqueFields
PK_RegionNoYesRegionID

Table: 'dbo.Shippers', 3 fields (132 bytes)

Field NameType and SizeDefaultNullable
ShipperIDint identity NOT NULL
CompanyNamenvarchar(40) NOT NULL
Phonenvarchar(24) NULL

Primary key for table 'dbo.Shippers'

Primary Key NameFields
PK_ShippersShipperID

Indexes for table 'dbo.Shippers', 1 item

Index NameClusteredUniqueFields
PK_ShippersYesYesShipperID

Dependencies for table 'dbo.Shippers', 1 item

Object NameType
dbo.Invoicesview

Table: 'dbo.Suppliers', 12 fields (530 bytes)

Field NameType and SizeDefaultNullable
SupplierIDint identity NOT NULL
CompanyNamenvarchar(40) NOT NULL
ContactNamenvarchar(30) NULL
ContactTitlenvarchar(30) NULL
Addressnvarchar(60) NULL
Citynvarchar(15) NULL
Regionnvarchar(15) NULL
PostalCodenvarchar(10) NULL
Countrynvarchar(15) NULL
Phonenvarchar(24) NULL
Faxnvarchar(24) NULL
HomePagentext NULL

Primary key for table 'dbo.Suppliers'

Primary Key NameFields
PK_SuppliersSupplierID

Indexes for table 'dbo.Suppliers', 3 items

Index NameClusteredUniqueFields
PK_SuppliersYesYesSupplierID
CompanyNameNoNoCompanyName
PostalCodeNoNoPostalCode

Dependencies for table 'dbo.Suppliers', 1 item

Object NameType
dbo.Customer and Suppliers by Cityview

Table: 'dbo.Territories', 3 fields (144 bytes)

Field NameType and SizeDefaultNullable
TerritoryIDnvarchar(20) NOT NULL
TerritoryDescriptionnchar(50) NOT NULL
RegionIDint NOT NULL

Primary key for table 'dbo.Territories'

Primary Key NameFields
PK_TerritoriesTerritoryID

Foreign keys for table 'dbo.Territories', 1 item

ForeignPrimary
Territories.RegionIDRegion.RegionID

Indexes for table 'dbo.Territories', 1 item

Index NameClusteredUniqueFields
PK_TerritoriesNoYesTerritoryID

Views, 16 items

View: 'dbo."Alphabetical list of products"'

ColumnType and Size
ProductIDint NOT NULL
ProductNamenvarchar(40) NOT NULL
SupplierIDint NULL
CategoryIDint NULL
QuantityPerUnitnvarchar(20) NULL
UnitPricemoney(19.4) NULL
UnitsInStocksmallint NULL
UnitsOnOrdersmallint NULL
ReorderLevelsmallint NULL
Discontinuedbit NOT NULL
CategoryNamenvarchar(15) NOT NULL

View: 'dbo."Category Sales for 1997"'

ColumnType and Size
CategoryNamenvarchar(15) NOT NULL
CategorySalesmoney(19.4) NULL

View: 'dbo."Current Product List"'

ColumnType and Size
ProductIDint identity NOT NULL
ProductNamenvarchar(40) NOT NULL

View: 'dbo."Customer and Suppliers by City"'

ColumnType and Size
Citynvarchar(15) NULL
CompanyNamenvarchar(40) NOT NULL
ContactNamenvarchar(30) NULL
Relationshipvarchar(9) NOT NULL

View: 'dbo.Invoices'

ColumnType and Size
ShipNamenvarchar(40) NULL
ShipAddressnvarchar(60) NULL
ShipCitynvarchar(15) NULL
ShipRegionnvarchar(15) NULL
ShipPostalCodenvarchar(10) NULL
ShipCountrynvarchar(15) NULL
CustomerIDnchar(5) NULL
CustomerNamenvarchar(40) NOT NULL
Addressnvarchar(60) NULL
Citynvarchar(15) NULL
Regionnvarchar(15) NULL
PostalCodenvarchar(10) NULL
Countrynvarchar(15) NULL
Salespersonnvarchar(31) NOT NULL
OrderIDint NOT NULL
OrderDatedatetime NULL
RequiredDatedatetime NULL
ShippedDatedatetime NULL
ShipperNamenvarchar(40) NOT NULL
ProductIDint NOT NULL
ProductNamenvarchar(40) NOT NULL
UnitPricemoney(19.4) NOT NULL
Quantitysmallint NOT NULL
Discountreal NOT NULL
ExtendedPricemoney(19.4) NULL
Freightmoney(19.4) NULL

View: 'dbo."Order Details Extended"'

ColumnType and Size
OrderIDint NOT NULL
ProductIDint NOT NULL
ProductNamenvarchar(40) NOT NULL
UnitPricemoney(19.4) NOT NULL
Quantitysmallint NOT NULL
Discountreal NOT NULL
ExtendedPricemoney(19.4) NULL

View: 'dbo."Order Subtotals"'

ColumnType and Size
OrderIDint NOT NULL
Subtotalmoney(19.4) NULL

View: 'dbo."Orders Qry"'

ColumnType and Size
OrderIDint NOT NULL
CustomerIDnchar(5) NULL
EmployeeIDint NULL
OrderDatedatetime NULL
RequiredDatedatetime NULL
ShippedDatedatetime NULL
ShipViaint NULL
Freightmoney(19.4) NULL
ShipNamenvarchar(40) NULL
ShipAddressnvarchar(60) NULL
ShipCitynvarchar(15) NULL
ShipRegionnvarchar(15) NULL
ShipPostalCodenvarchar(10) NULL
ShipCountrynvarchar(15) NULL
CompanyNamenvarchar(40) NOT NULL
Addressnvarchar(60) NULL
Citynvarchar(15) NULL
Regionnvarchar(15) NULL
PostalCodenvarchar(10) NULL
Countrynvarchar(15) NULL

View: 'dbo."Product Sales for 1997"'

ColumnType and Size
CategoryNamenvarchar(15) NOT NULL
ProductNamenvarchar(40) NOT NULL
ProductSalesmoney(19.4) NULL

View: 'dbo."Products Above Average Price"'

ColumnType and Size
ProductNamenvarchar(40) NOT NULL
UnitPricemoney(19.4) NULL

View: 'dbo."Products by Category"'

ColumnType and Size
CategoryNamenvarchar(15) NOT NULL
ProductNamenvarchar(40) NOT NULL
QuantityPerUnitnvarchar(20) NULL
UnitsInStocksmallint NULL
Discontinuedbit NOT NULL

View: 'dbo."Quarterly Orders"'

ColumnType and Size
CustomerIDnchar(5) NULL
CompanyNamenvarchar(40) NULL
Citynvarchar(15) NULL
Countrynvarchar(15) NULL

View: 'dbo."Sales Totals by Amount"'

ColumnType and Size
SaleAmountmoney(19.4) NULL
OrderIDint NOT NULL
CompanyNamenvarchar(40) NOT NULL
ShippedDatedatetime NULL

View: 'dbo."Sales by Category"'

ColumnType and Size
CategoryIDint NOT NULL
CategoryNamenvarchar(15) NOT NULL
ProductNamenvarchar(40) NOT NULL
ProductSalesmoney(19.4) NULL

View: 'dbo."Summary of Sales by Quarter"'

ColumnType and Size
ShippedDatedatetime NULL
OrderIDint NOT NULL
Subtotalmoney(19.4) NULL

View: 'dbo."Summary of Sales by Year"'

ColumnType and Size
ShippedDatedatetime NULL
OrderIDint NOT NULL
Subtotalmoney(19.4) NULL

Procedures, 8 items

Procedure NameNoteParameters
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 nameCodeSizeNullableSearchableScale
sql_variant-1508000YESexcept LIKE 
uniqueidentifier-1136YESexcept LIKE 
ntext-101073741823YESLIKE ONLY 
nvarchar-94000YESYES 
sysname-9128NOYES 
nchar-84000YESYES 
bit-71YESexcept LIKE 
tinyint-63YESexcept LIKE 
tinyint identity-63NOexcept LIKE 
bigint-519YESexcept LIKE 
bigint identity-519NOexcept LIKE 
image-42147483647YESNO 
varbinary-38000YESexcept LIKE 
binary-28000YESexcept LIKE 
timestamp-28NOexcept LIKE 
text-12147483647YESLIKE ONLY 
char18000YESYES 
numeric238YESexcept LIKE0..38
numeric() identity238NOexcept LIKE 
decimal338YESexcept LIKE0..38
money319YESexcept LIKE 
smallmoney310YESexcept LIKE 
decimal() identity338NOexcept LIKE 
int410YESexcept LIKE 
int identity410NOexcept LIKE 
smallint55YESexcept LIKE 
smallint identity55NOexcept LIKE 
float615YESexcept LIKE 
real77YESexcept LIKE 
datetime1123YESYES 
smalldatetime1116YESYES 
varchar128000YESYES