Database Schema Report
Property | Value |
Report created | 02-Nov-2005 19:01 |
DBMS and version | Microsoft SQL Server [ 08.00.0760 ] |
Server | TOSHIBA2 |
Database name | Northwind |
User/Login | |
Schema/Owner | dbo |
Driver and version | SQLSRV32.DLL [ 03.85.1117 ] |
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 Name | Fields |
PK_Categories | CategoryID |
Index Name | Clustered | Unique | Fields |
PK_Categories | Yes | Yes | CategoryID |
CategoryName | No | No | CategoryName |
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 |
Field Name | Type and Size | Default | Nullable |
CustomerID | nchar(5) | NOT NULL | |
CustomerTypeID | nchar(10) | NOT NULL |
Primary Key Name | Fields |
PK_CustomerCustomerDemo | CustomerID, CustomerTypeID |
Foreign | Primary |
CustomerCustomerDemo.CustomerTypeID | CustomerDemographics.CustomerTypeID |
CustomerCustomerDemo.CustomerID | Customers.CustomerID |
Index Name | Clustered | Unique | Fields |
PK_CustomerCustomerDemo | No | Yes | CustomerID, CustomerTypeID |
Field Name | Type and Size | Default | Nullable |
CustomerTypeID | nchar(10) | NOT NULL | |
CustomerDesc | ntext | NULL |
Primary Key Name | Fields |
PK_CustomerDemographics | CustomerTypeID |
Index Name | Clustered | Unique | Fields |
PK_CustomerDemographics | No | Yes | CustomerTypeID |
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 Name | Fields |
PK_Customers | CustomerID |
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 |
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 |
Field Name | Type and Size | Default | Nullable |
YDC_ID | float | NULL | |
Description of Program | text | NULL |
Field Name | Type and Size | Default | Nullable |
YDC_ID | float | NULL | |
Description of Program | text | NULL |
Field Name | Type and Size | Default | Nullable |
EmployeeID | int | NOT NULL | |
TerritoryID | nvarchar(20) | NOT NULL |
Primary Key Name | Fields |
PK_EmployeeTerritories | EmployeeID, TerritoryID |
Foreign | Primary |
EmployeeTerritories.EmployeeID | Employees.EmployeeID |
EmployeeTerritories.TerritoryID | Territories.TerritoryID |
Index Name | Clustered | Unique | Fields |
PK_EmployeeTerritories | No | Yes | EmployeeID, TerritoryID |
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 Name | Fields |
PK_Employees | EmployeeID |
Foreign | Primary |
Employees.ReportsTo | Employees.EmployeeID |
Index Name | Clustered | Unique | Fields |
PK_Employees | Yes | Yes | EmployeeID |
LastName | No | No | LastName |
PostalCode | No | No | PostalCode |
Object Name | Type |
dbo.CK_Birthdate | check cns |
dbo.Employee Sales by Country | stored procedure |
dbo.Invoices | view |
Field Name | Type and Size | Default | Nullable |
ReviewText | varchar(255) | NULL | |
ReviewerID | varchar(255) | NULL |
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 Name | Fields |
PK_Order_Details | OrderID, ProductID |
Foreign | Primary |
Order Details.OrderID | Orders.OrderID |
Order Details.ProductID | Products.ProductID |
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 |
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 Name | Fields |
PK_Orders | OrderID |
Foreign | Primary |
Orders.CustomerID | Customers.CustomerID |
Orders.EmployeeID | Employees.EmployeeID |
Orders.ShipVia | Shippers.ShipperID |
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 |
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 |
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 Name | Fields |
PK_Products | ProductID |
Foreign | Primary |
Products.CategoryID | Categories.CategoryID |
Products.SupplierID | Suppliers.SupplierID |
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 |
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 |
Field Name | Type and Size | Default | Nullable |
RegionID | int | NOT NULL | |
RegionDescription | nchar(50) | NOT NULL |
Primary Key Name | Fields |
PK_Region | RegionID |
Index Name | Clustered | Unique | Fields |
PK_Region | No | Yes | RegionID |
Field Name | Type and Size | Default | Nullable |
ShipperID | int identity | NOT NULL | |
CompanyName | nvarchar(40) | NOT NULL | |
Phone | nvarchar(24) | NULL |
Primary Key Name | Fields |
PK_Shippers | ShipperID |
Index Name | Clustered | Unique | Fields |
PK_Shippers | Yes | Yes | ShipperID |
Object Name | Type |
dbo.Invoices | view |
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 Name | Fields |
PK_Suppliers | SupplierID |
Index Name | Clustered | Unique | Fields |
PK_Suppliers | Yes | Yes | SupplierID |
CompanyName | No | No | CompanyName |
PostalCode | No | No | PostalCode |
Object Name | Type |
dbo.Customer and Suppliers by City | view |
Field Name | Type and Size | Default | Nullable |
TerritoryID | nvarchar(20) | NOT NULL | |
TerritoryDescription | nchar(50) | NOT NULL | |
RegionID | int | NOT NULL |
Primary Key Name | Fields |
PK_Territories | TerritoryID |
Foreign | Primary |
Territories.RegionID | Region.RegionID |
Index Name | Clustered | Unique | Fields |
PK_Territories | No | Yes | TerritoryID |
Field Name | Type and Size | Default | Nullable |
A | int | NULL | |
B | int | NULL |
Object Name | Type |
dbo.B5 | check cns |
Field Name | Type and Size | Default | Nullable |
A | timestamp(8) | NULL | |
A2 | int | (1) | NULL |
Field Name | Type and Size | Default | Nullable |
YDC_ID | float | NOT NULL | |
Description of Program | text | NULL |
Primary Key Name | Fields |
SpecialColumn | YDC_ID |
Index Name | Clustered | Unique | Fields |
PK | Yes | Yes | YDC_ID |
Field Name | Type and Size | Default | Nullable |
YDC_ID | float | NULL | |
Description of Program | text | NULL |
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 |
Column | Type and Size |
CategoryName | nvarchar(15) NOT NULL |
CategorySales | money(19.4) NULL |
Column | Type and Size |
ProductID | int identity NOT NULL |
ProductName | nvarchar(40) NOT NULL |
Column | Type and Size |
City | nvarchar(15) NULL |
CompanyName | nvarchar(40) NOT NULL |
ContactName | nvarchar(30) NULL |
Relationship | varchar(9) NOT NULL |
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 |
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 |
Column | Type and Size |
OrderID | int NOT NULL |
Subtotal | money(19.4) NULL |
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 |
Column | Type and Size |
CategoryName | nvarchar(15) NOT NULL |
ProductName | nvarchar(40) NOT NULL |
ProductSales | money(19.4) NULL |
Column | Type and Size |
ProductName | nvarchar(40) NOT NULL |
UnitPrice | money(19.4) NULL |
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 |
Column | Type and Size |
CustomerID | nchar(5) NULL |
CompanyName | nvarchar(40) NULL |
City | nvarchar(15) NULL |
Country | nvarchar(15) NULL |
Column | Type and Size |
SaleAmount | money(19.4) NULL |
OrderID | int NOT NULL |
CompanyName | nvarchar(40) NOT NULL |
ShippedDate | datetime NULL |
Column | Type and Size |
CategoryID | int NOT NULL |
CategoryName | nvarchar(15) NOT NULL |
ProductName | nvarchar(40) NOT NULL |
ProductSales | money(19.4) NULL |
Column | Type and Size |
ShippedDate | datetime NULL |
OrderID | int NOT NULL |
Subtotal | money(19.4) NULL |
Column | Type and Size |
ShippedDate | datetime NULL |
OrderID | int NOT NULL |
Subtotal | money(19.4) NULL |
Column | Type and Size |
YDC_ID | float NULL |
Description of Program | text NULL |
Procedure Name | Parameters |
CustOrderHist | @RETURN_VALUE int(10) [RETURN VALUE],@CustomerID nchar(5) [INPUT] |
CustOrdersDetail | @RETURN_VALUE int(10) [RETURN VALUE],@OrderID int(10) [INPUT] |
CustOrdersOrders | @RETURN_VALUE int(10) [RETURN VALUE],@CustomerID nchar(5) [INPUT] |
Employee Sales by Country | @RETURN_VALUE int(10) [RETURN VALUE],@Beginning_Date datetime(23) [INPUT],@Ending_Date datetime(23) [INPUT] |
Sales by Year | @RETURN_VALUE int(10) [RETURN VALUE],@Beginning_Date datetime(23) [INPUT],@Ending_Date datetime(23) [INPUT] |
SalesByCategory | @RETURN_VALUE int(10) [RETURN VALUE],@CategoryName nvarchar(15) [INPUT],@OrdYear nvarchar(4) [INPUT] |
Ten Most Expensive Products | @RETURN_VALUE int(10) [RETURN VALUE] |
TestSalesByStore | @RETURN_VALUE int(10) [RETURN VALUE] |
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 |