Northwind database 
(30/03/2009) 

Table: dbo.Categories
List of categoriesSecond line 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CategoryID	int			Categories of Northwind products.	
IX 	CategoryName	nvarchar  (15)			Name of food category.	
	Description	ntext	Yes		Full description of the category. Provide samples.	
	Picture	image	Yes		A picture representing the food category.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.Products	FK_Products_Categories	PK_Categories	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
CategoryName	CategoryName	ASC		NONCLUSTERED	
PK_Categories	CategoryID	ASC	Yes	CLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

Extended properties: 

Property name	Value	
Author	John Smith	
CustomProperty1	Sample value to test extended properties	

 Objects that depend on dbo.Categories: 

dbo.Alphabetical list of products 
dbo.Product Sales for 1997 
dbo.Products by Category 
dbo.Sales by Category 
dbo.SalesByCategory 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Categories]( 
         [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
         [CategoryName] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [Description] [ntext] COLLATE Modern_Spanish_CI_AS NULL, 
         [Picture] [image] NULL 
     ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
      
     Table: dbo.CustomerCustomerDemo
Test description 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	CustomerID	nchar  (5)			234	
    PK FK	CustomerTypeID	nchar  (10)			234	

Foreign keys dependencies: 

FK_CustomerCustomerDemo_Customers: CustomerID relies upon remote PK_Customers    (dbo.Customers)   
FK_CustomerCustomerDemo: CustomerTypeID relies upon remote PK_CustomerDemographics    (dbo.CustomerDemographics)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_CustomerCustomerDemo	CustomerID	ASC	Yes	NONCLUSTERED	
PK_CustomerCustomerDemo	CustomerTypeID	ASC	Yes	NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[CustomerCustomerDemo]( 
         [CustomerID] [nchar](5) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [CustomerTypeID] [nchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.CustomerDemographics
Sample annotation for table CustomerDemographics 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CustomerTypeID	nchar  (10)			123465	
	CustomerDesc	ntext	Yes		sdfg	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.CustomerCustomerDemo	FK_CustomerCustomerDemo	PK_CustomerDemographics	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_CustomerDemographics	CustomerTypeID	ASC	Yes	NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[CustomerDemographics]( 
         [CustomerTypeID] [nchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [CustomerDesc] [ntext] COLLATE Modern_Spanish_CI_AS NULL 
     ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
      
     Table: dbo.Customers
Customers' names and related data 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CustomerID	nchar  (5)			Unique five-character code based on customer name.	
IX 	CompanyName	nvarchar  (40)			sdfg	
	ContactName	nvarchar  (30)	Yes		sdg	
	ContactTitle	nvarchar  (30)	Yes		sdfg	
	Address	nvarchar  (60)	Yes		Street or post-office box.	
IX 	City	nvarchar  (15)	Yes			
IX 	Region	nvarchar  (15)	Yes		State or province.	
IX 	PostalCode	nvarchar  (10)	Yes			
	Country	nvarchar  (15)	Yes			
	Phone	nvarchar  (24)	Yes		Phone number includes country code or area code.	
	Fax	nvarchar  (24)	Yes		Phone number includes country code or area code.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.CustomerCustomerDemo	FK_CustomerCustomerDemo_Customers	PK_Customers	
dbo.Orders	FK_Orders_Customers	PK_Customers	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
City	City	ASC		NONCLUSTERED	
CompanyName	CompanyName	ASC		NONCLUSTERED	
IX_Customers	CompanyName	ASC		NONCLUSTERED	
PK_Customers	CustomerID	ASC	Yes	CLUSTERED	
PostalCode	PostalCode	ASC		NONCLUSTERED	
Region	Region	ASC		NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

Extended properties: 

Property name	Value	
Author	John Smith	
CustomProperty1	Sample value to test extended properties	

 Objects that depend on dbo.Customers: 

dbo.Customer and Suppliers by City 
dbo.CustOrderHist 
dbo.Invoices 
dbo.Orders Qry 
dbo.Quarterly Orders 
dbo.Sales Totals by Amount 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Customers]( 
         [CustomerID] [nchar](5) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [CompanyName] [nvarchar](40) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [ContactName] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NULL, 
         [ContactTitle] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NULL, 
         [Address] [nvarchar](60) COLLATE Modern_Spanish_CI_AS NULL, 
         [City] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [Region] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [PostalCode] [nvarchar](10) COLLATE Modern_Spanish_CI_AS NULL, 
         [Country] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [Phone] [nvarchar](24) COLLATE Modern_Spanish_CI_AS NULL, 
         [Fax] [nvarchar](24) COLLATE Modern_Spanish_CI_AS NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.Employees
Employees' names, titles, and personal information. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	EmployeeID	int			Number automatically assigned to new employee.	
IX 	LastName	nvarchar  (20)				
	FirstName	nvarchar  (10)				
	Title	nvarchar  (30)	Yes		Employee's title.	
	TitleOfCourtesy	nvarchar  (25)	Yes			
	BirthDate	datetime	Yes			
	HireDate	datetime	Yes			
	Address	nvarchar  (60)	Yes			Street or post-office box.	
	City	nvarchar  (15)	Yes			
	Region	nvarchar  (15)	Yes		Street or post-office box.	
IX 	PostalCode	nvarchar  (10)	Yes			
	Country	nvarchar  (15)	Yes			
	HomePhone	nvarchar  (24)	Yes		Phone number includes country code or area code.	
	Extension	nvarchar  (4)	Yes			Internal telephone extension number.	
	Photo	image	Yes		Picture of employee.	
	Notes	ntext	Yes		General information about employee's background.	
FK 	ReportsTo	int	Yes			Employee's supervisor.	
	PhotoPath	nvarchar  (255)	Yes			

Foreign keys dependencies: 

FK_Employees_Employees: ReportsTo relies upon remote PK_Employees    (dbo.Employees)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.Employees	FK_Employees_Employees	PK_Employees	
dbo.EmployeeTerritories	FK_EmployeeTerritories_Employees	PK_Employees	
dbo.Orders	FK_Orders_Employees	PK_Employees	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
LastName	LastName	ASC		NONCLUSTERED	
PK_Employees	EmployeeID	ASC	Yes	CLUSTERED	
PostalCode	PostalCode	ASC		NONCLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Birthdate	BirthDate	([BirthDate] < getdate())	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

 Objects that depend on dbo.Employees: 

dbo.Employee Sales by Country 
dbo.Invoices 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Employees]( 
         [EmployeeID] [int] IDENTITY(1,1) NOT NULL, 
         [LastName] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [FirstName] [nvarchar](10) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [Title] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NULL, 
         [TitleOfCourtesy] [nvarchar](25) COLLATE Modern_Spanish_CI_AS NULL, 
         [BirthDate] [datetime] NULL, 
         [HireDate] [datetime] NULL, 
         [Address] [nvarchar](60) COLLATE Modern_Spanish_CI_AS NULL, 
         [City] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [Region] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [PostalCode] [nvarchar](10) COLLATE Modern_Spanish_CI_AS NULL, 
         [Country] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [HomePhone] [nvarchar](24) COLLATE Modern_Spanish_CI_AS NULL, 
         [Extension] [nvarchar](4) COLLATE Modern_Spanish_CI_AS NULL, 
         [Photo] [image] NULL, 
         [Notes] [ntext] COLLATE Modern_Spanish_CI_AS NULL, 
         [ReportsTo] [int] NULL, 
         [PhotoPath] [nvarchar](255) COLLATE Modern_Spanish_CI_AS NULL 
     ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
      
     Table: dbo.EmployeeTerritories

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	EmployeeID	int				
    PK FK	TerritoryID	nvarchar  (20)				

Foreign keys dependencies: 

FK_EmployeeTerritories_Employees: EmployeeID relies upon remote PK_Employees    (dbo.Employees)   
FK_EmployeeTerritories_Territories: TerritoryID relies upon remote PK_Territories    (dbo.Territories)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_EmployeeTerritories	EmployeeID	ASC	Yes	NONCLUSTERED	
PK_EmployeeTerritories	TerritoryID	ASC	Yes	NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[EmployeeTerritories]( 
         [EmployeeID] [int] NOT NULL, 
         [TerritoryID] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.Order Details
Details on products, quantities, and prices for each order in the Orders table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	OrderID	int			Same as Order ID in Orders table.	
    PK FK	ProductID	int			Same as Product ID in Products table.	
	UnitPrice	money		(0)		
	Quantity	smallint		(1)		
	Discount	real		(0)		

Foreign keys dependencies: 

FK_Order_Details_Orders: OrderID relies upon remote PK_Orders    (dbo.Orders)   
FK_Order_Details_Products: ProductID relies upon remote PK_Products    (dbo.Products)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
OrderID	OrderID	ASC		NONCLUSTERED	
OrdersOrder_Details	OrderID	ASC		NONCLUSTERED	
PK_Order_Details	OrderID	ASC	Yes	CLUSTERED	
PK_Order_Details	ProductID	ASC	Yes	CLUSTERED	
ProductID	ProductID	ASC		NONCLUSTERED	
ProductsOrder_Details	ProductID	ASC		NONCLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Discount	Discount	([Discount] >= 0 and [Discount] <= 1)	
CK_Quantity	Quantity	([Quantity] > 0)	
CK_UnitPrice	UnitPrice	([UnitPrice] >= 0)	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

 Objects that depend on dbo.Order Details: 

dbo.CustOrderHist 
dbo.CustOrdersDetail 
dbo.Invoices 
dbo.Order Details Extended 
dbo.Order Subtotals 
dbo.Product Sales for 1997 
dbo.SalesByCategory 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Order Details]( 
         [OrderID] [int] NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [UnitPrice] [money] NOT NULL, 
         [Quantity] [smallint] NOT NULL, 
         [Discount] [real] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.Orders
Customer name, order date, and freight charge for each order. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	OrderID	int				
FK 	CustomerID	nchar  (5)	Yes			
FK 	EmployeeID	int	Yes		Same entry as in Employees table.	
IX 	OrderDate	datetime	Yes			
	RequiredDate	datetime	Yes			
IX 	ShippedDate	datetime	Yes			
FK 	ShipVia	int	Yes		Same as Shipper ID in Shippers table.	
	Freight	money	Yes	(0)		
	ShipName	nvarchar  (40)	Yes		Name of person or company to receive the shipment.	
	ShipAddress	nvarchar  (60)	Yes		Street address only -- no post-office box allowed.	
	ShipCity	nvarchar  (15)	Yes			
	ShipRegion	nvarchar  (15)	Yes		State or province.	
IX 	ShipPostalCode	nvarchar  (10)	Yes			
	ShipCountry	nvarchar  (15)	Yes			

Foreign keys dependencies: 

FK_Orders_Customers: CustomerID relies upon remote PK_Customers    (dbo.Customers)   
FK_Orders_Employees: EmployeeID relies upon remote PK_Employees    (dbo.Employees)   
FK_Orders_Shippers: ShipVia relies upon remote PK_Shippers    (dbo.Shippers)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.Order Details	FK_Order_Details_Orders	PK_Orders	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
CustomerID	CustomerID	ASC		NONCLUSTERED	
CustomersOrders	CustomerID	ASC		NONCLUSTERED	
EmployeeID	EmployeeID	ASC		NONCLUSTERED	
EmployeesOrders	EmployeeID	ASC		NONCLUSTERED	
OrderDate	OrderDate	ASC		NONCLUSTERED	
PK_Orders	OrderID	ASC	Yes	CLUSTERED	
ShippedDate	ShippedDate	ASC		NONCLUSTERED	
ShippersOrders	ShipVia	ASC		NONCLUSTERED	
ShipPostalCode	ShipPostalCode	ASC		NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

 Objects that depend on dbo.Orders: 

dbo.CustOrderHist 
dbo.CustOrdersOrders 
dbo.Employee Sales by Country 
dbo.Invoices 
dbo.Orders Qry 
dbo.Product Sales for 1997 
dbo.Quarterly Orders 
dbo.Sales by Category 
dbo.Sales by Year 
dbo.Sales Totals by Amount 
dbo.SalesByCategory 
dbo.Summary of Sales by Quarter 
dbo.Summary of Sales by Year 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Orders]( 
         [OrderID] [int] IDENTITY(1,1) NOT NULL, 
         [CustomerID] [nchar](5) COLLATE Modern_Spanish_CI_AS NULL, 
         [EmployeeID] [int] NULL, 
         [OrderDate] [datetime] NULL, 
         [RequiredDate] [datetime] NULL, 
         [ShippedDate] [datetime] NULL, 
         [ShipVia] [int] NULL, 
         [Freight] [money] NULL, 
         [ShipName] [nvarchar](40) COLLATE Modern_Spanish_CI_AS NULL, 
         [ShipAddress] [nvarchar](60) COLLATE Modern_Spanish_CI_AS NULL, 
         [ShipCity] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [ShipRegion] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [ShipPostalCode] [nvarchar](10) COLLATE Modern_Spanish_CI_AS NULL, 
         [ShipCountry] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.Products
Product names, suppliers, prices, and units in stock. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductID	int			Number automatically assigned to new product.	
IX 	ProductName	nvarchar  (40)				
FK 	SupplierID	int	Yes		Same entry as in Suppliers table.	
FK 	CategoryID	int	Yes		Same entry as in Categories table.	
	QuantityPerUnit	nvarchar  (20)	Yes		(e.g., 24-count case, 1-liter bottle).	
	UnitPrice	money	Yes	(0)		
	UnitsInStock	smallint	Yes	(0)		
	UnitsOnOrder	smallint	Yes	(0)		
	ReorderLevel	smallint	Yes	(0)	Minimum units to maintain in stock.	
	Discontinued	bit		(0)	Yes means item is no longer available.	

Foreign keys dependencies: 

FK_Products_Suppliers: SupplierID relies upon remote PK_Suppliers    (dbo.Suppliers)   
FK_Products_Categories: CategoryID relies upon remote PK_Categories    (dbo.Categories)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.Order Details	FK_Order_Details_Products	PK_Products	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
CategoriesProducts	CategoryID	ASC		NONCLUSTERED	
CategoryID	CategoryID	ASC		NONCLUSTERED	
PK_Products	ProductID	ASC	Yes	CLUSTERED	
ProductName	ProductName	ASC		NONCLUSTERED	
SupplierID	SupplierID	ASC		NONCLUSTERED	
SuppliersProducts	SupplierID	ASC		NONCLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Products_UnitPrice	UnitPrice	([UnitPrice] >= 0)	
CK_ReorderLevel	ReorderLevel	([ReorderLevel] >= 0)	
CK_UnitsInStock	UnitsInStock	([UnitsInStock] >= 0)	
CK_UnitsOnOrder	UnitsOnOrder	([UnitsOnOrder] >= 0)	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

 Objects that depend on dbo.Products: 

dbo.Alphabetical list of products 
dbo.Current Product List 
dbo.CustOrderHist 
dbo.CustOrdersDetail 
dbo.Invoices 
dbo.Order Details Extended 
dbo.Product Sales for 1997 
dbo.Products Above Average Price 
dbo.Products by Category 
dbo.Sales by Category 
dbo.SalesByCategory 
dbo.Ten Most Expensive Products 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Products]( 
         [ProductID] [int] IDENTITY(1,1) NOT NULL, 
         [ProductName] [nvarchar](40) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [SupplierID] [int] NULL, 
         [CategoryID] [int] NULL, 
         [QuantityPerUnit] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NULL, 
         [UnitPrice] [money] NULL, 
         [UnitsInStock] [smallint] NULL, 
         [UnitsOnOrder] [smallint] NULL, 
         [ReorderLevel] [smallint] NULL, 
         [Discontinued] [bit] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.Region
Sample comment add to test dbdescThis is the second line 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	RegionID	int				
	RegionDescription	nchar  (50)				

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.Territories	FK_Territories_Region	PK_Region	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_Region	RegionID	ASC	Yes	NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Region]( 
         [RegionID] [int] NOT NULL, 
         [RegionDescription] [nchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.Shippers
Shippers' names and phone numbers. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ShipperID	int				Number automatically assigned to new shipper.	
	CompanyName	nvarchar  (40)			Name of shipping company.	
	Phone	nvarchar  (24)	Yes		Phone number includes country code or area code.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.Orders	FK_Orders_Shippers	PK_Shippers	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_Shippers	ShipperID	ASC	Yes	CLUSTERED	

 Objects that depend on dbo.Shippers: 

dbo.Invoices 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Shippers]( 
         [ShipperID] [int] IDENTITY(1,1) NOT NULL, 
         [CompanyName] [nvarchar](40) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [Phone] [nvarchar](24) COLLATE Modern_Spanish_CI_AS NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.Suppliers
Suppliers' names, addresses, phone numbers, and hyperlinks to home pages. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	SupplierID	int			Number automatically assigned to new supplier.	
IX 	CompanyName	nvarchar  (40)				
	ContactName	nvarchar  (30)	Yes			
	ContactTitle	nvarchar  (30)	Yes			
	Address	nvarchar  (60)	Yes		Street or post-office box.	
	City	nvarchar  (15)	Yes			
	Region	nvarchar  (15)	Yes		State or province.	
IX 	PostalCode	nvarchar  (10)	Yes			
	Country	nvarchar  (15)	Yes			
	Phone	nvarchar  (24)	Yes		Phone number includes country code or area code.	
	Fax	nvarchar  (24)	Yes		Phone number includes country code or area code.	
	HomePage	ntext	Yes		Supplier's home page on World Wide Web.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.Products	FK_Products_Suppliers	PK_Suppliers	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
CompanyName	CompanyName	ASC		NONCLUSTERED	
PK_Suppliers	SupplierID	ASC	Yes	CLUSTERED	
PostalCode	PostalCode	ASC		NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

 Objects that depend on dbo.Suppliers: 

dbo.Customer and Suppliers by City 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Suppliers]( 
         [SupplierID] [int] IDENTITY(1,1) NOT NULL, 
         [CompanyName] [nvarchar](40) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [ContactName] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NULL, 
         [ContactTitle] [nvarchar](30) COLLATE Modern_Spanish_CI_AS NULL, 
         [Address] [nvarchar](60) COLLATE Modern_Spanish_CI_AS NULL, 
         [City] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [Region] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [PostalCode] [nvarchar](10) COLLATE Modern_Spanish_CI_AS NULL, 
         [Country] [nvarchar](15) COLLATE Modern_Spanish_CI_AS NULL, 
         [Phone] [nvarchar](24) COLLATE Modern_Spanish_CI_AS NULL, 
         [Fax] [nvarchar](24) COLLATE Modern_Spanish_CI_AS NULL, 
         [HomePage] [ntext] COLLATE Modern_Spanish_CI_AS NULL 
     ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
      
     Table: dbo.Territories
sdfsf 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	TerritoryID	nvarchar  (20)			sdfg	
	TerritoryDescription	nchar  (50)			sdfg	
FK 	RegionID	int			sdfg	

Foreign keys dependencies: 

FK_Territories_Region: RegionID relies upon remote PK_Region    (dbo.Region)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
dbo.EmployeeTerritories	FK_EmployeeTerritories_Territories	PK_Territories	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_Territories	TerritoryID	ASC	Yes	NONCLUSTERED	

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant		grant	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[Territories]( 
         [TerritoryID] [nvarchar](20) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [TerritoryDescription] [nchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL, 
         [RegionID] [int] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.TestTable
This is a test table 2 

 	Field name	Data type	Nullable	Default	Comments	
	TestCustomField	TestType  (20)	Yes		sdcs	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[TestTable]( 
         [TestCustomField] [TestType] NULL 
     ) ON [PRIMARY] 

View: dbo.Alphabetical list of products  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Products	ProductID	int			
	dbo.Products	ProductName	nvarchar  (40)			
	dbo.Products	SupplierID	int	Yes		
	dbo.Products	CategoryID	int	Yes		
	dbo.Products	QuantityPerUnit	nvarchar  (20)	Yes		
	dbo.Products	UnitPrice	money	Yes		
	dbo.Products	UnitsInStock	smallint	Yes		
	dbo.Products	UnitsOnOrder	smallint	Yes		
	dbo.Products	ReorderLevel	smallint	Yes		
	dbo.Products	Discontinued	bit			
	dbo.Categories	CategoryName	nvarchar  (15)			

create view "Alphabetical list of products" AS 
     SELECT Products.*, Categories.CategoryName 
     FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID 
     WHERE (((Products.Discontinued)=0)) 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Products dbo.Categories 

View: dbo.Category Sales for 1997  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Totals product sales by category based on values returned by the Product Sales for 1997 query. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Product Sales for 1997	CategoryName	nvarchar  (15)		CatName	
		CategorySales	money	Yes	CatSales	

create view "Category Sales for 1997" AS 
     SELECT "Product Sales for 1997".CategoryName, Sum("Product Sales for 1997".ProductSales) AS CategorySales 
     FROM "Product Sales for 1997" 
     GROUP BY "Product Sales for 1997".CategoryName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Product Sales for 1997 

View: dbo.Current Product List  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Filters records in Products table; query returns only products that are not discontinued. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Products	ProductID	int			
	dbo.Products	ProductName	nvarchar  (40)			

create view "Current Product List" AS 
     SELECT Product_List.ProductID, Product_List.ProductName 
     FROM Products AS Product_List 
     WHERE (((Product_List.Discontinued)=0)) 
     --ORDER BY Product_List.ProductName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Products 

View: dbo.Customer and Suppliers by City  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Customers	City	nvarchar  (15)	Yes		
	dbo.Customers	CompanyName	nvarchar  (40)			
	dbo.Customers	ContactName	nvarchar  (30)	Yes		
		Relationship	varchar  (9)			

create view "Customer and Suppliers by City" AS 
     SELECT City, CompanyName, ContactName, 'Customers' AS Relationship  
     FROM Customers 
     UNION SELECT City, CompanyName, ContactName, 'Suppliers' 
     FROM Suppliers 
     --ORDER BY City, CompanyName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Suppliers dbo.Customers 

View: dbo.Invoices  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
(Criteria) Record source for Invoice report. Based on six tables. Includes expressions that concatenate first and last employee name and that use the CCur function to calculate extended price. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Orders	ShipName	nvarchar  (40)	Yes	Name of the shipper	
	dbo.Orders	ShipAddress	nvarchar  (60)	Yes		
	dbo.Orders	ShipCity	nvarchar  (15)	Yes		
	dbo.Orders	ShipRegion	nvarchar  (15)	Yes		
	dbo.Orders	ShipPostalCode	nvarchar  (10)	Yes		
	dbo.Orders	ShipCountry	nvarchar  (15)	Yes		
	dbo.Orders	CustomerID	nchar  (5)	Yes		
		CustomerName	nvarchar  (40)			
	dbo.Customers	Address	nvarchar  (60)	Yes		
	dbo.Customers	City	nvarchar  (15)	Yes		
	dbo.Customers	Region	nvarchar  (15)	Yes		
	dbo.Customers	PostalCode	nvarchar  (10)	Yes		
	dbo.Customers	Country	nvarchar  (15)	Yes		
		Salesperson	nvarchar  (31)			
	dbo.Orders	OrderID	int			
	dbo.Orders	OrderDate	datetime	Yes		
	dbo.Orders	RequiredDate	datetime	Yes		
	dbo.Orders	ShippedDate	datetime	Yes		
		ShipperName	nvarchar  (40)			
	dbo.Products	ProductID	int			
	dbo.Products	ProductName	nvarchar  (40)			
	dbo.Order Details	UnitPrice	money			
	dbo.Order Details	Quantity	smallint			
	dbo.Order Details	Discount	real			
		ExtendedPrice	money	Yes		
	dbo.Orders	Freight	money	Yes		

create view Invoices AS 
     SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,  
     	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,  
     	Customers.Region, Customers.PostalCode, Customers.Country,  
     	(FirstName + ' ' + LastName) AS Salesperson,  
     	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,  
     	"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,  
     	"Order Details".Discount,  
     	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)100))*100) AS ExtendedPrice, Orders.Freight 
     FROM 	Shippers INNER JOIN  
     		(Products INNER JOIN  
     			( 
     				(Employees INNER JOIN  
     					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)  
     				ON Employees.EmployeeID = Orders.EmployeeID)  
     			INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)  
     		ON Products.ProductID = "Order Details".ProductID)  
     	ON Shippers.ShipperID = Orders.ShipVia 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Customers dbo.Shippers dbo.Employees dbo.Order Details dbo.Products dbo.Orders 

View: dbo.Order Details Extended  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for several forms and reports. Uses CCur function to compute the ExtendedPrice for each item ordered. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Order Details	OrderID	int			
	dbo.Products	ProductID	int			
	dbo.Products	ProductName	nvarchar  (40)			
	dbo.Order Details	UnitPrice	money			
	dbo.Order Details	Quantity	smallint			
	dbo.Order Details	Discount	real			
		ExtendedPrice	money	Yes		

create view "Order Details Extended" AS 
     SELECT "Order Details".OrderID, "Order Details".ProductID, Products.ProductName,  
     	"Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount,  
     	(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)100))*100) AS ExtendedPrice 
     FROM Products INNER JOIN "Order Details" ON Products.ProductID = "Order Details".ProductID 
     --ORDER BY "Order Details".OrderID 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Order Details dbo.Products dbo.Sales by Category 

View: dbo.Order Subtotals  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for other queries. Uses Sum and CCur functions to compute subtotal for each order. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Order Details	OrderID	int			
		Subtotal	money	Yes		

create view "Order Subtotals" AS 
     SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)100))*100) AS Subtotal 
     FROM "Order Details" 
     GROUP BY "Order Details".OrderID 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Order Details dbo.Employee Sales by Country dbo.Sales by Year dbo.Sales Totals by Amount dbo.Summary of Sales by Quarter dbo.Summary of Sales by Year 

View: dbo.Orders Qry  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Orders	OrderID	int			
	dbo.Orders	CustomerID	nchar  (5)	Yes		
	dbo.Orders	EmployeeID	int	Yes		
	dbo.Orders	OrderDate	datetime	Yes		
	dbo.Orders	RequiredDate	datetime	Yes		
	dbo.Orders	ShippedDate	datetime	Yes		
	dbo.Orders	ShipVia	int	Yes		
	dbo.Orders	Freight	money	Yes		
	dbo.Orders	ShipName	nvarchar  (40)	Yes		
	dbo.Orders	ShipAddress	nvarchar  (60)	Yes		
	dbo.Orders	ShipCity	nvarchar  (15)	Yes		
	dbo.Orders	ShipRegion	nvarchar  (15)	Yes		
	dbo.Orders	ShipPostalCode	nvarchar  (10)	Yes		
	dbo.Orders	ShipCountry	nvarchar  (15)	Yes		
	dbo.Customers	CompanyName	nvarchar  (40)			
	dbo.Customers	Address	nvarchar  (60)	Yes		
	dbo.Customers	City	nvarchar  (15)	Yes		
	dbo.Customers	Region	nvarchar  (15)	Yes		
	dbo.Customers	PostalCode	nvarchar  (10)	Yes		
	dbo.Customers	Country	nvarchar  (15)	Yes		

create view "Orders Qry" AS 
     SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate,  
     	Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,  
     	Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,  
     	Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country 
     FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Customers dbo.Orders 

View: dbo.Product Sales for 1997  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for Category Sales for 1997 query. Uses Sum and CCur functions. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Categories	CategoryName	nvarchar  (15)			
	dbo.Products	ProductName	nvarchar  (40)			
		ProductSales	money	Yes		

create view "Product Sales for 1997" AS 
     SELECT Categories.CategoryName, Products.ProductName,  
     Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)100))*100) AS ProductSales 
     FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)  
     	INNER JOIN (Orders  
     		INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)  
     	ON Products.ProductID = "Order Details".ProductID 
     WHERE (((Orders.ShippedDate) Between '19970101' And '19971231')) 
     GROUP BY Categories.CategoryName, Products.ProductName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Order Details dbo.Categories dbo.Products dbo.Orders dbo.Category Sales for 1997 

View: dbo.Products Above Average Price  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
(Subquery) Returns products that have a unit price higher than the average. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Products	ProductName	nvarchar  (40)			
	dbo.Products	UnitPrice	money	Yes		

create view "Products Above Average Price" AS 
     SELECT Products.ProductName, Products.UnitPrice 
     FROM Products 
     WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products) 
     --ORDER BY Products.UnitPrice DESC 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Products 

View: dbo.Products by Category  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Categories	CategoryName	nvarchar  (15)			
	dbo.Products	ProductName	nvarchar  (40)			
	dbo.Products	QuantityPerUnit	nvarchar  (20)	Yes		
	dbo.Products	UnitsInStock	smallint	Yes		
	dbo.Products	Discontinued	bit			

create view "Products by Category" AS 
     SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued 
     FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID 
     WHERE Products.Discontinued <> 1 
     --ORDER BY Categories.CategoryName, Products.ProductName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Categories dbo.Products 

View: dbo.Quarterly Orders  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for Quarterly Orders form. Lists only customers who had orders in 1995. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Orders	CustomerID	nchar  (5)	Yes		
	dbo.Customers	CompanyName	nvarchar  (40)	Yes		
	dbo.Customers	City	nvarchar  (15)	Yes		
	dbo.Customers	Country	nvarchar  (15)	Yes		

create view "Quarterly Orders" AS 
     SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country 
     FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
     WHERE Orders.OrderDate BETWEEN '19970101' And '19971231' 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Customers dbo.Orders 

View: dbo.Sales by Category  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for Sales by Category report. 

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Products	CategoryID	int			
	dbo.Categories	CategoryName	nvarchar  (15)			
	dbo.Products	ProductName	nvarchar  (40)			
		ProductSales	money	Yes		

create view "Sales by Category" AS 
     SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName,  
     	Sum("Order Details Extended".ExtendedPrice) AS ProductSales 
     FROM 	Categories INNER JOIN  
     		(Products INNER JOIN  
     			(Orders INNER JOIN "Order Details Extended" ON Orders.OrderID = "Order Details Extended".OrderID)  
     		ON Products.ProductID = "Order Details Extended".ProductID)  
     	ON Categories.CategoryID = Products.CategoryID 
     WHERE Orders.OrderDate BETWEEN '19970101' And '19971231' 
     GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName 
     --ORDER BY Products.ProductName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Categories dbo.Products dbo.Orders dbo.Order Details Extended 

View: dbo.Sales Totals by Amount  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

 	Source	Field name	Data type	Nullable	Comments	
		SaleAmount	money	Yes		
	dbo.Orders	OrderID	int			
	dbo.Customers	CompanyName	nvarchar  (40)			
	dbo.Orders	ShippedDate	datetime	Yes		

create view "Sales Totals by Amount" AS 
     SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate 
     FROM 	Customers INNER JOIN  
     		(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)  
     	ON Customers.CustomerID = Orders.CustomerID 
     WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231') 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Customers dbo.Orders dbo.Order Subtotals 

View: dbo.Summary of Sales by Quarter  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Orders	ShippedDate	datetime	Yes		
	dbo.Orders	OrderID	int			
	dbo.Order Subtotals	Subtotal	money	Yes		

create view "Summary of Sales by Quarter" AS 
     SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal 
     FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID 
     WHERE Orders.ShippedDate IS NOT NULL 
     --ORDER BY Orders.ShippedDate 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Orders dbo.Order Subtotals 

View: dbo.Summary of Sales by Year  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

 	Source	Field name	Data type	Nullable	Comments	
	dbo.Orders	ShippedDate	datetime	Yes		
	dbo.Orders	OrderID	int			
	dbo.Order Subtotals	Subtotal	money	Yes		

create view "Summary of Sales by Year" AS 
     SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal 
     FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID 
     WHERE Orders.ShippedDate IS NOT NULL 
     --ORDER BY Orders.ShippedDate 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public	grant	grant	grant	grant			

Related objects:  dbo.Orders dbo.Order Subtotals 

Stored Procedure: dbo.CustOrderHist  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
This description has been extracted from the procedure definition  

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@CustomerID	nchar	This is stored procedure parameter description	

/* This description has been extracted from the procedure definition */CREATE  PROCEDURE CustOrderHist @CustomerID nchar(5) 
     AS 
     SELECT ProductName, Total=SUM(Quantity) 
     FROM Products P, [Order Details] OD, Orders O, Customers C 
     WHERE C.CustomerID = @CustomerID 
     AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID 
     GROUP BY ProductName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public					grant		

Related objects:  dbo.Order Details dbo.Customers dbo.Products dbo.Orders 

Stored Procedure: dbo.CustOrdersDetail  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for several forms and reports. Uses CCur function to compute the ExtendedPrice for each item ordered. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@OrderID	int		

 
     CREATE PROCEDURE CustOrdersDetail @OrderID int 
     AS 
     SELECT ProductName, 
         UnitPrice=ROUND(Od.UnitPrice, 2), 
         Quantity, 
         Discount=CONVERT(int, Discount * 100),  
         ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2) 
     FROM Products P, [Order Details] Od 
     WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public					grant		

Related objects:  dbo.Order Details dbo.Products 

Stored Procedure: dbo.CustOrdersOrders  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@CustomerID	nchar		

 
     CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5) 
     AS 
     SELECT OrderID,  
     	OrderDate, 
     	RequiredDate, 
     	ShippedDate 
     FROM Orders 
     WHERE CustomerID = @CustomerID 
     ORDER BY OrderID 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public					grant		

Related objects:  dbo.Orders 

Stored Procedure: dbo.Employee Sales by Country  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
 Record source for Employee Sales by Country report. Prompts for beginning and ending dates. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@Beginning_Date	datetime		
IN	@Ending_Date	datetime		

create procedure "Employee Sales by Country"  
     @Beginning_Date DateTime, @Ending_Date DateTime AS 
     SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount 
     FROM Employees INNER JOIN  
     	(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)  
     	ON Employees.EmployeeID = Orders.EmployeeID 
     WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public					grant		

Related objects:  dbo.Employees dbo.Orders dbo.Order Subtotals 

Stored Procedure: dbo.Sales by Year  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for Sales by Year report and its subreport. Refers to controls on Sales by Year Dialog for parameter values. Uses Format function to return the year value of a shipped date. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@Beginning_Date	datetime		
IN	@Ending_Date	datetime		

create procedure "Sales by Year"  
     	@Beginning_Date DateTime, @Ending_Date DateTime AS 
     SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year 
     FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID 
     WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public					grant		

Related objects:  dbo.Orders dbo.Order Subtotals 

Stored Procedure: dbo.SalesByCategory  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Record source for Sales by Category report. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@CategoryName	nvarchar		
IN	@OrdYear	nvarchar		

CREATE PROCEDURE SalesByCategory 
         @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998' 
     AS 
     IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'  
     BEGIN 
     	SELECT @OrdYear = '1998' 
     END 
      
     SELECT ProductName, 
     	TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0) 
     FROM [Order Details] OD, Orders O, Products P, Categories C 
     WHERE OD.OrderID = O.OrderID  
     	AND OD.ProductID = P.ProductID  
     	AND P.CategoryID = C.CategoryID 
     	AND C.CategoryName = @CategoryName 
     	AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear 
     GROUP BY ProductName 
     ORDER BY ProductName 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public					grant		

Related objects:  dbo.Order Details dbo.Categories dbo.Products dbo.Orders 

Stored Procedure: dbo.Ten Most Expensive Products  ( Created: 06 Ago 2000 Last altered: 06 Ago 2000 )
Returns 10 most expensive products. Specifies the number of highest values to return using the Top Values box on the toolbar. 

create procedure "Ten Most Expensive Products" AS 
     SET ROWCOUNT 10 
     SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice 
     FROM Products 
     ORDER BY Products.UnitPrice DESC 

Table permissions: 

User name / role	SELECT	INSERT	UPDATE	DELETE	EXECUTE	DRI	
public					grant		

Related objects:  dbo.Products 

User-defined data types 

 - TestType (varchar (20), not null )  

USERS 

User name	Login name	Created	Roles	
dbo	sa	06 Ago 2000	db_owner 
	
guest		06 Ago 2000		

ROLES 

Role name	App role	Users in this role	
db_accessadmin			
db_backupoperator			
db_datareader			
db_datawriter			
db_ddladmin			
db_denydatareader			
db_denydatawriter			
db_owner		dbo 
	
db_securityadmin			
public