Adventure Works Database Schema

Table of Contents

• Summary
• Tables
1. dbo.AWBuildVersion
2. dbo.DatabaseLog
3. dbo.ErrorLog
4. HumanResources.Department
5. HumanResources.Employee
6. HumanResources.EmployeeAddress
7. HumanResources.EmployeeDepartmentHistory
8. HumanResources.EmployeePayHistory
9. HumanResources.JobCandidate
10. HumanResources.Shift
11. Person.Address
12. Person.AddressType
13. Person.Contact
14. Person.ContactType
15. Person.CountryRegion
16. Person.StateProvince
17. Production.BillOfMaterials
18. Production.Culture
19. Production.Document
20. Production.Illustration
21. Production.Location
22. Production.Product
23. Production.ProductCategory
24. Production.ProductCostHistory
25. Production.ProductDescription
26. Production.ProductDocument
27. Production.ProductInventory
28. Production.ProductListPriceHistory
29. Production.ProductModel
30. Production.ProductModelIllustration
31. Production.ProductModelProductDescriptionCulture
32. Production.ProductPhoto
33. Production.ProductProductPhoto
34. Production.ProductReview
35. Production.ProductSubcategory
36. Production.ScrapReason
37. Production.TransactionHistory
38. Production.TransactionHistoryArchive
39. Production.UnitMeasure
40. Production.WorkOrder
41. Production.WorkOrderRouting
42. Purchasing.ProductVendor
43. Purchasing.PurchaseOrderDetail
44. Purchasing.PurchaseOrderHeader
45. Purchasing.ShipMethod
46. Purchasing.Vendor
47. Purchasing.VendorAddress
48. Purchasing.VendorContact
49. Sales.ContactCreditCard
50. Sales.CountryRegionCurrency
51. Sales.CreditCard
52. Sales.Currency
53. Sales.CurrencyRate
54. Sales.Customer
55. Sales.CustomerAddress
56. Sales.Individual
57. Sales.SalesOrderDetail
58. Sales.SalesOrderHeader
59. Sales.SalesOrderHeaderSalesReason
60. Sales.SalesPerson
61. Sales.SalesPersonQuotaHistory
62. Sales.SalesReason
63. Sales.SalesTaxRate
64. Sales.SalesTerritory
65. Sales.SalesTerritoryHistory
66. Sales.ShoppingCartItem
67. Sales.SpecialOffer
68. Sales.SpecialOfferProduct
69. Sales.Store
70. Sales.StoreContact
• Views
1. HumanResources.vEmployee
2. HumanResources.vEmployeeDepartment
3. HumanResources.vEmployeeDepartmentHistory
4. HumanResources.vJobCandidate
5. HumanResources.vJobCandidateEducation
6. HumanResources.vJobCandidateEmployment
7. Person.vAdditionalContactInfo
8. Person.vStateProvinceCountryRegion
9. Production.vProductAndDescription
10. Production.vProductModelCatalogDescription
11. Production.vProductModelInstructions
12. Purchasing.vVendor
13. Sales.vIndividualCustomer
14. Sales.vIndividualDemographics
15. Sales.vSalesPerson
16. Sales.vSalesPersonSalesByFiscalYears
17. Sales.vStoreWithDemographics
• Procedures and Functions
1. dbo.ufnGetAccountingEndDate
2. dbo.ufnGetAccountingStartDate
3. dbo.ufnGetContactInformation
4. dbo.ufnGetDocumentStatusText
5. dbo.ufnGetProductDealerPrice
6. dbo.ufnGetProductListPrice
7. dbo.ufnGetProductStandardCost
8. dbo.ufnGetPurchaseOrderStatusText
9. dbo.ufnGetSalesOrderStatusText
10. dbo.ufnGetStock
11. dbo.ufnLeadingZeros
12. dbo.uspGetBillOfMaterials
13. dbo.uspGetEmployeeManagers
14. dbo.uspGetManagerEmployees
15. dbo.uspGetWhereUsedProductID
16. dbo.uspLogError
17. dbo.uspPrintError
18. HumanResources.uspUpdateEmployeeHireInfo
19. HumanResources.uspUpdateEmployeeLogin
20. HumanResources.uspUpdateEmployeePersonalInfo
• Rules
• Database Level Triggers
• Data Types

Summary

Property	Value
Report created	20-May-2011 15:49
DBMS and version	Microsoft SQL Server [ 09.00.1399 ]
Server	DTM-XP
Database name	AdventureWorks
User/Login	sa
Schema/Owner
Driver and version	sqlncli10.dll [ 10.00.4000 ]
DTM Schema Reporter	Version 1.24.23, (C) 2003-2011 DTM soft.

Tables, 70 items

Table: 'dbo.AWBuildVersion'

4 fields. 83 bytes.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
SystemInformationID	tinyint identity		tinyint		Not null	Primary key for AWBuildVersion records.
Database Version	nvarchar	25			Not null	Version number of the database in 9.yy.mm.dd.00 format.
VersionDate	datetime				Not null	Date and time the record was last updated.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'dbo.AWBuildVersion'

Primary Key Name	Field Name
PK_AWBuildVersion_SystemInformationID	SystemInformationID

There are no Foreign Keys for this table.

Indexes for table 'dbo.AWBuildVersion', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_AWBuildVersion_SystemInformationID	Clustered index created by a primary key constraint.	Yes	Yes	SystemInformationID

Triggers for table 'dbo.AWBuildVersion', 1 item

Name	Description	Type	Enabled
uAWBuildVersion	AFTER UPDATE trigger setting the ModifiedDate column in the AWBuildVersion table to the current date.	after Update	Yes

Dependencies for table 'dbo.AWBuildVersion', 1 item

Object Name	Type	Field Name
dbo.uAWBuildVersion	trigger	N/A

Table: 'dbo.DatabaseLog'

8 fields. 1044 bytes.

Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
DatabaseLogID	int identity		int		Not null	Primary key for DatabaseLog records.
PostTime	datetime				Not null	The date and time the DDL change occurred.
DatabaseUser	sysname	128	nvarchar(128)		Not null	The user who implemented the DDL change.
Event	sysname	128	nvarchar(128)		Not null	The type of DDL statement that was executed.
Schema	sysname	128	nvarchar(128)		Null	The schema to which the changed object belongs.
Object	sysname	128	nvarchar(128)		Null	The object that was changed by the DDL statment.
TSQL	nvarchar	max			Not null	The exact Transact-SQL statement that was executed.
XmlEvent	xml				Not null	The raw XML data generated by database trigger.

Extended Properties

The object has no extended properties.

Primary key for table 'dbo.DatabaseLog'

Primary Key Name	Field Name
PK_DatabaseLog_DatabaseLogID	DatabaseLogID

There are no Foreign Keys for this table.

Indexes for table 'dbo.DatabaseLog', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_DatabaseLog_DatabaseLogID	Nonclustered index created by a primary key constraint.	No	Yes	DatabaseLogID

There are no Triggers for this table.
There are no Dependencies for this object.

Table: 'dbo.ErrorLog'

9 fields. 8544 bytes.

Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ErrorLogID	int identity		int		Not null	Primary key for ErrorLog records.
ErrorTime	datetime			getdate()	Not null	The date and time at which the error occurred.
UserName	sysname	128	nvarchar(128)		Not null	The user who executed the batch in which the error occurred.
ErrorNumber	int				Not null	The error number of the error that occurred.
ErrorSeverity	int				Null	The severity of the error that occurred.
ErrorState	int				Null	The state number of the error that occurred.
ErrorProcedure	nvarchar	126			Null	The name of the stored procedure or trigger where the error occurred.
ErrorLine	int				Null	The line number at which the error occurred.
ErrorMessage	nvarchar	4000			Not null	The message text of the error that occurred.

Extended Properties

The object has no extended properties.

Primary key for table 'dbo.ErrorLog'

Primary Key Name	Field Name
PK_ErrorLog_ErrorLogID	ErrorLogID

There are no Foreign Keys for this table.

Indexes for table 'dbo.ErrorLog', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ErrorLog_ErrorLogID	Clustered index created by a primary key constraint.	Yes	Yes	ErrorLogID

There are no Triggers for this table.

Dependencies for table 'dbo.ErrorLog', 1 item

Object Name	Type	Field Name
dbo.uspLogError	stored procedure	N/A

Table: 'HumanResources.Department'

4 fields. 218 bytes.

Lookup table containing the departments within the Adventure Works Cycles company.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
DepartmentID	smallint identity		smallint		Not null	Primary key for Department records.
Name	Name		nvarchar(50)		Not null	Name of the department.
GroupName	Name		nvarchar(50)		Not null	Name of the group to which the department belongs.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.Department'

Primary Key Name	Field Name
PK_Department_DepartmentID	DepartmentID

There are no Foreign Keys for this table.

Indexes for table 'HumanResources.Department', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_Department_DepartmentID	Clustered index created by a primary key constraint.	Yes	Yes	DepartmentID
AK_Department_Name	Unique nonclustered index.	No	Yes	Name

Triggers for table 'HumanResources.Department', 1 item

Name	Description	Type	Enabled
uDepartment	AFTER UPDATE trigger setting the ModifiedDate column in the Department table to the current date.	after Update	Yes

Dependencies for table 'HumanResources.Department', 3 items

Object Name	Type	Field Name
HumanResources.uDepartment	trigger	N/A
HumanResources.vEmployeeDepartment	view	N/A
HumanResources.vEmployeeDepartmentHistory	view	N/A

Table: 'HumanResources.Employee'

16 fields. 728 bytes.

Employee information such as salary, department, and title.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
EmployeeID	int identity		int		Not null	Primary key for Employee records.
NationalIDNumber	nvarchar	15			Not null	Unique national identification number such as a social security number.
ContactID	int				Not null	Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID	nvarchar	256			Not null	Network login.
ManagerID	int				Null	Manager to whom the employee is assigned. Foreign Key to Employee.M
Title	nvarchar	50			Not null	Work title such as Buyer or Sales Representative.
BirthDate	datetime				Not null	Date of birth.	([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
MaritalStatus	nchar	1			Not null	M = Married, S = Single	(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
Gender	nchar	1			Not null	M = Male, F = Female	(upper([Gender])='F' OR upper([Gender])='M')
HireDate	datetime				Not null	Employee hired on this date.	([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
SalariedFlag	Flag		bit	(1)	Not null	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours	smallint			(0)	Not null	Number of available vacation hours.	([VacationHours]>=(-40) AND [VacationHours]<=(240))
SickLeaveHours	smallint			(0)	Not null	Number of available sick leave hours.	([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))
CurrentFlag	Flag		bit	(1)	Not null	0 = Inactive, 1 = Active
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.Employee'

Primary Key Name	Field Name
PK_Employee_EmployeeID	EmployeeID

Foreign keys for table 'HumanResources.Employee', 2 items

Foreign	Primary	Key Name
Employee.ManagerID	Employee.EmployeeID	FK_Employee_Employee_ManagerID
Employee.ContactID	Contact.ContactID	FK_Employee_Contact_ContactID

Indexes for table 'HumanResources.Employee', 5 items

Index Name	Description	Clustered	Unique	Fields
PK_Employee_EmployeeID	Clustered index created by a primary key constraint.	Yes	Yes	EmployeeID
AK_Employee_LoginID	Unique nonclustered index.	No	Yes	LoginID
AK_Employee_NationalIDNumber	Unique nonclustered index.	No	Yes	NationalIDNumber
AK_Employee_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid
IX_Employee_ManagerID	Nonclustered index.	No	No	ManagerID

Triggers for table 'HumanResources.Employee', 2 items

Name	Description	Type	Enabled
dEmployee	INSTEAD OF DELETE trigger which keeps Employees from being deleted.	instead of Delete	Yes
uEmployee	AFTER UPDATE trigger setting the ModifiedDate column in the Employee table to the current date.	after Update	Yes

Dependencies for table 'HumanResources.Employee', 18 items

Object Name	Type	Field Name
dbo.ufnGetContactInformation	table function	N/A
dbo.uspGetEmployeeManagers	stored procedure	N/A
dbo.uspGetManagerEmployees	stored procedure	N/A
HumanResources.CK_Employee_BirthDate	check cns	N/A
HumanResources.CK_Employee_Gender	check cns	N/A
HumanResources.CK_Employee_HireDate	check cns	N/A
HumanResources.CK_Employee_MaritalStatus	check cns	N/A
HumanResources.CK_Employee_SickLeaveHours	check cns	N/A
HumanResources.CK_Employee_VacationHours	check cns	N/A
HumanResources.uEmployee	trigger	N/A
HumanResources.uspUpdateEmployeeHireInfo	stored procedure	N/A
HumanResources.uspUpdateEmployeeLogin	stored procedure	N/A
HumanResources.uspUpdateEmployeePersonalInfo	stored procedure	N/A
HumanResources.vEmployee	view	N/A
HumanResources.vEmployeeDepartment	view	N/A
HumanResources.vEmployeeDepartmentHistory	view	N/A
Sales.vSalesPerson	view	N/A
Sales.vSalesPersonSalesByFiscalYears	view	N/A

Table: 'HumanResources.EmployeeAddress'

4 fields. 40 bytes.

Cross-reference table mapping employees to their address(es).

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
EmployeeID	int				Not null	Primary key. Foreign key to Employee.EmployeeID.
AddressID	int				Not null	Primary key. Foreign key to Address.AddressID.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.EmployeeAddress'

Primary Key Name	Field Names
PK_EmployeeAddress_EmployeeID_AddressID	EmployeeID, AddressID

Foreign keys for table 'HumanResources.EmployeeAddress', 2 items

Foreign	Primary	Key Name
EmployeeAddress.EmployeeID	Employee.EmployeeID	FK_EmployeeAddress_Employee_EmployeeID
EmployeeAddress.AddressID	Address.AddressID	FK_EmployeeAddress_Address_AddressID

Indexes for table 'HumanResources.EmployeeAddress', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_EmployeeAddress_EmployeeID_AddressID	Clustered index created by a primary key constraint.	Yes	Yes	EmployeeID, AddressID
AK_EmployeeAddress_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid

Triggers for table 'HumanResources.EmployeeAddress', 1 item

Name	Description	Type	Enabled
uEmployeeAddress	AFTER UPDATE trigger setting the ModifiedDate column in the EmployeeAddress table to the current date.	after Update	Yes

Dependencies for table 'HumanResources.EmployeeAddress', 3 items

Object Name	Type	Field Name
HumanResources.uEmployeeAddress	trigger	N/A
HumanResources.vEmployee	view	N/A
Sales.vSalesPerson	view	N/A

Table: 'HumanResources.EmployeeDepartmentHistory'

6 fields. 55 bytes.

Employee department transfers.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
EmployeeID	int				Not null	Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID	smallint				Not null	Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID	tinyint				Not null	Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDate	datetime				Not null	Date the employee started work in the department.
EndDate	datetime				Null	Date the employee left the department. NULL = Current department.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.EmployeeDepartmentHistory'

Primary Key Name	Field Names
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	EmployeeID, StartDate, DepartmentID, ShiftID

Foreign keys for table 'HumanResources.EmployeeDepartmentHistory', 3 items

Foreign	Primary	Key Name
EmployeeDepartmentHistory.DepartmentID	Department.DepartmentID	FK_EmployeeDepartmentHistory_Department_DepartmentID
EmployeeDepartmentHistory.EmployeeID	Employee.EmployeeID	FK_EmployeeDepartmentHistory_Employee_EmployeeID
EmployeeDepartmentHistory.ShiftID	Shift.ShiftID	FK_EmployeeDepartmentHistory_Shift_ShiftID

Indexes for table 'HumanResources.EmployeeDepartmentHistory', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	Clustered index created by a primary key constraint.	Yes	Yes	EmployeeID, StartDate, DepartmentID, ShiftID
IX_EmployeeDepartmentHistory_DepartmentID	Nonclustered index.	No	No	DepartmentID
IX_EmployeeDepartmentHistory_ShiftID	Nonclustered index.	No	No	ShiftID

Triggers for table 'HumanResources.EmployeeDepartmentHistory', 1 item

Name	Description	Type	Enabled
uEmployeeDepartmentHistory	AFTER UPDATE trigger setting the ModifiedDate column in the EmployeeDepartmentHistory table to the current date.	after Update	Yes

Dependencies for table 'HumanResources.EmployeeDepartmentHistory', 4 items

Object Name	Type	Field Name
HumanResources.CK_EmployeeDepartmentHistory_EndDate	check cns	N/A
HumanResources.uEmployeeDepartmentHistory	trigger	N/A
HumanResources.vEmployeeDepartment	view	N/A
HumanResources.vEmployeeDepartmentHistory	view	N/A

Table: 'HumanResources.EmployeePayHistory'

5 fields. 58 bytes.

Employee pay history.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
EmployeeID	int				Not null	Employee identification number. Foreign key to Employee.EmployeeID.
RateChangeDate	datetime				Not null	Date the change in pay is effective
Rate	money	19,4	decimal(19,4)		Not null	Salary hourly rate.	([Rate]>=(6.50) AND [Rate]<=(200.00))
PayFrequency	tinyint				Not null	1 = Salary received monthly, 2 = Salary received biweekly	([PayFrequency]=(2) OR [PayFrequency]=(1))
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.EmployeePayHistory'

Primary Key Name	Field Names
PK_EmployeePayHistory_EmployeeID_RateChangeDate	EmployeeID, RateChangeDate

Foreign keys for table 'HumanResources.EmployeePayHistory', 1 item

Foreign	Primary	Key Name
EmployeePayHistory.EmployeeID	Employee.EmployeeID	FK_EmployeePayHistory_Employee_EmployeeID

Indexes for table 'HumanResources.EmployeePayHistory', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_EmployeePayHistory_EmployeeID_RateChangeDate	Clustered index created by a primary key constraint.	Yes	Yes	EmployeeID, RateChangeDate

Triggers for table 'HumanResources.EmployeePayHistory', 1 item

Name	Description	Type	Enabled
uEmployeePayHistory	AFTER UPDATE trigger setting the ModifiedDate column in the EmployeePayHistory table to the current date.	after Update	Yes

Dependencies for table 'HumanResources.EmployeePayHistory', 4 items

Object Name	Type	Field Name
HumanResources.CK_EmployeePayHistory_PayFrequency	check cns	N/A
HumanResources.CK_EmployeePayHistory_Rate	check cns	N/A
HumanResources.uEmployeePayHistory	trigger	N/A
HumanResources.uspUpdateEmployeeHireInfo	stored procedure	N/A

Table: 'HumanResources.JobCandidate'

4 fields. 24 bytes.

Resumes submitted to Human Resources by job applicants.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
JobCandidateID	int identity		int		Not null	Primary key for JobCandidate records.
EmployeeID	int				Null	Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID.
Resume	xml				Null	Resume in XML format.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.JobCandidate'

Primary Key Name	Field Name
PK_JobCandidate_JobCandidateID	JobCandidateID

Foreign keys for table 'HumanResources.JobCandidate', 1 item

Foreign	Primary	Key Name
JobCandidate.EmployeeID	Employee.EmployeeID	FK_JobCandidate_Employee_EmployeeID

Indexes for table 'HumanResources.JobCandidate', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_JobCandidate_JobCandidateID	Clustered index created by a primary key constraint.	Yes	Yes	JobCandidateID
IX_JobCandidate_EmployeeID	Nonclustered index.	No	No	EmployeeID

Triggers for table 'HumanResources.JobCandidate', 1 item

Name	Description	Type	Enabled
uJobCandidate	AFTER UPDATE trigger setting the ModifiedDate column in the JobCandidat table to the current date.	after Update	Yes

Dependencies for table 'HumanResources.JobCandidate', 4 items

Object Name	Type	Field Name
HumanResources.uJobCandidate	trigger	N/A
HumanResources.vJobCandidate	view	N/A
HumanResources.vJobCandidateEducation	view	N/A
HumanResources.vJobCandidateEmployment	view	N/A

Table: 'HumanResources.Shift'

5 fields. 149 bytes.

Work shift lookup table.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ShiftID	tinyint identity		tinyint		Not null	Primary key for Shift records.
Name	Name		nvarchar(50)		Not null	Shift description.
StartTime	datetime				Not null	Shift start time.
EndTime	datetime				Not null	Shift end time.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.Shift'

Primary Key Name	Field Name
PK_Shift_ShiftID	ShiftID

There are no Foreign Keys for this table.

Indexes for table 'HumanResources.Shift', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_Shift_ShiftID	Clustered index created by a primary key constraint.	Yes	Yes	ShiftID
AK_Shift_Name	Unique nonclustered index.	No	Yes	Name
AK_Shift_StartTime_EndTime	Unique nonclustered index.	No	Yes	StartTime, EndTime

Triggers for table 'HumanResources.Shift', 1 item

Name	Description	Type	Enabled
uShift	AFTER UPDATE trigger setting the ModifiedDate column in the Shift table to the current date.	after Update	Yes

Dependencies for table 'HumanResources.Shift', 2 items

Object Name	Type	Field Name
HumanResources.uShift	trigger	N/A
HumanResources.vEmployeeDepartmentHistory	view	N/A

Table: 'Person.Address'

8 fields. 370 bytes.

Street address information for customers, employees, and vendors.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
AddressID	int identity		int		Not null	Primary key for Address records.
AddressLine1	nvarchar	60			Not null	First street address line.
AddressLine2	nvarchar	60			Null	Second street address line.
City	nvarchar	30			Not null	Name of the city.
StateProvinceID	int				Not null	Unique identification number for the state or province. Foreign key to StateProvince table.
PostalCode	nvarchar	15			Not null	Postal code for the street address.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Person.Address'

Primary Key Name	Field Name
PK_Address_AddressID	AddressID

Foreign keys for table 'Person.Address', 1 item

Foreign	Primary	Key Name
Address.StateProvinceID	StateProvince.StateProvinceID	FK_Address_StateProvince_StateProvinceID

Indexes for table 'Person.Address', 4 items

Index Name	Description	Clustered	Unique	Fields
PK_Address_AddressID	Clustered index created by a primary key constraint.	Yes	Yes	AddressID
AK_Address_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	Nonclustered index.	No	Yes	AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
IX_Address_StateProvinceID	Nonclustered index.	No	No	StateProvinceID

Triggers for table 'Person.Address', 1 item

Name	Description	Type	Enabled
uAddress	AFTER UPDATE trigger setting the ModifiedDate column in the Address table to the current date.	after Update	Yes

Dependencies for table 'Person.Address', 6 items

Object Name	Type	Field Name
HumanResources.vEmployee	view	N/A
Person.uAddress	trigger	N/A
Purchasing.vVendor	view	N/A
Sales.vIndividualCustomer	view	N/A
Sales.vSalesPerson	view	N/A
Sales.vStoreWithDemographics	view	N/A

Table: 'Person.AddressType'

4 fields. 136 bytes.

Types of addresses stored in the Address table.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
AddressTypeID	int identity		int		Not null	Primary key for AddressType records.
Name	Name		nvarchar(50)		Not null	Address type description. For example, Billing, Home, or Shipping.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Person.AddressType'

Primary Key Name	Field Name
PK_AddressType_AddressTypeID	AddressTypeID

There are no Foreign Keys for this table.

Indexes for table 'Person.AddressType', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_AddressType_AddressTypeID	Clustered index created by a primary key constraint.	Yes	Yes	AddressTypeID
AK_AddressType_Name	Unique nonclustered index.	No	Yes	Name
AK_AddressType_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid

Triggers for table 'Person.AddressType', 1 item

Name	Description	Type	Enabled
uAddressType	AFTER UPDATE trigger setting the ModifiedDate column in the AddressType table to the current date.	after Update	Yes

Dependencies for table 'Person.AddressType', 3 items

Object Name	Type	Field Name
Person.uAddressType	trigger	N/A
Sales.vIndividualCustomer	view	N/A
Sales.vStoreWithDemographics	view	N/A

Table: 'Person.Contact'

15 fields. 577 bytes.

Names of each employee, customer contact, and vendor contact.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ContactID	int identity		int		Not null	Primary key for Contact records.
NameStyle	NameStyle		bit	(0)	Not null	0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.
Title	nvarchar	8			Null	A courtesy title. For example, Mr. or Ms.
FirstName	Name		nvarchar(50)		Not null	First name of the person.
MiddleName	Name		nvarchar(50)		Null	Middle name or middle initial of the person.
LastName	Name		nvarchar(50)		Not null	Last name of the person.
Suffix	nvarchar	10			Null	Surname suffix. For example, Sr. or Jr.
EmailAddress	nvarchar	50			Null	E-mail address for the person.
EmailPromotion	int			(0)	Not null	0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
Phone	Phone		nvarchar(25)		Null	Phone number associated with the person.
PasswordHash	varchar	40			Not null	Password for the e-mail account.
PasswordSalt	varchar	10			Not null	Random value concatenated with the password string before the password is hashed.
AdditionalContactInfo	xml				Null	Additional contact information about the person stored in xml format.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

Object Type	Object Name	Property Name	Property Value
TABLE	Contact	Author	Mike Filony

Primary key for table 'Person.Contact'

Primary Key Name	Field Name
PK_Contact_ContactID	ContactID

There are no Foreign Keys for this table.

Indexes for table 'Person.Contact', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_Contact_ContactID	Clustered index created by a primary key constraint.	Yes	Yes	ContactID
AK_Contact_rowguid	nonclustered, unique located on PRIMARY	No	Yes	rowguid
IX_Contact_EmailAddress	Nonclustered index.	No	No	EmailAddress

Triggers for table 'Person.Contact', 1 item

Name	Description	Type	Enabled
uContact	AFTER UPDATE trigger setting the ModifiedDate column in the Contact table to the current date.	after Update	Yes

Dependencies for table 'Person.Contact', 14 items

Object Name	Type	Field Name
dbo.ufnGetContactInformation	table function	N/A
dbo.uspGetEmployeeManagers	stored procedure	N/A
dbo.uspGetManagerEmployees	stored procedure	N/A
HumanResources.vEmployee	view	N/A
HumanResources.vEmployeeDepartment	view	N/A
HumanResources.vEmployeeDepartmentHistory	view	N/A
Person.CK_Contact_EmailPromotion	check cns	N/A
Person.uContact	trigger	N/A
Person.vAdditionalContactInfo	view	N/A
Purchasing.vVendor	view	N/A
Sales.vIndividualCustomer	view	N/A
Sales.vSalesPerson	view	N/A
Sales.vSalesPersonSalesByFiscalYears	view	N/A
Sales.vStoreWithDemographics	view	N/A

Table: 'Person.ContactType'

3 fields. 120 bytes.

Lookup table containing the types of contacts stored in Contact.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ContactTypeID	int identity		int		Not null	Primary key for ContactType records.
Name	Name		nvarchar(50)		Not null	Contact type description.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Person.ContactType'

Primary Key Name	Field Name
PK_ContactType_ContactTypeID	ContactTypeID

There are no Foreign Keys for this table.

Indexes for table 'Person.ContactType', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_ContactType_ContactTypeID	Clustered index created by a primary key constraint.	Yes	Yes	ContactTypeID
AK_ContactType_Name	Unique nonclustered index.	No	Yes	Name

Triggers for table 'Person.ContactType', 1 item

Name	Description	Type	Enabled
uContactType	AFTER UPDATE trigger setting the ModifiedDate column in the ContactType table to the current date.	after Update	Yes

Dependencies for table 'Person.ContactType', 4 items

Object Name	Type	Field Name
dbo.ufnGetContactInformation	table function	N/A
Person.uContactType	trigger	N/A
Purchasing.vVendor	view	N/A
Sales.vStoreWithDemographics	view	N/A

Table: 'Person.CountryRegion'

3 fields. 122 bytes.

Lookup table containing the ISO standard codes for countries and regions.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
CountryRegionCode	nvarchar	3			Not null	ISO standard code for countries and regions.
Name	Name		nvarchar(50)		Not null	Country or region name.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Person.CountryRegion'

Primary Key Name	Field Name
PK_CountryRegion_CountryRegionCode	CountryRegionCode

There are no Foreign Keys for this table.

Indexes for table 'Person.CountryRegion', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_CountryRegion_CountryRegionCode	Clustered index created by a primary key constraint.	Yes	Yes	CountryRegionCode
AK_CountryRegion_Name	Unique nonclustered index.	No	Yes	Name

Triggers for table 'Person.CountryRegion', 1 item

Name	Description	Type	Enabled
uCountryRegion	AFTER UPDATE trigger setting the ModifiedDate column in the CountryRegion table to the current date.	after Update	Yes

Dependencies for table 'Person.CountryRegion', 7 items

Object Name	Type	Field Name
HumanResources.vEmployee	view	N/A
Person.uCountryRegion	trigger	N/A
Person.vStateProvinceCountryRegion	view	N/A
Purchasing.vVendor	view	N/A
Sales.vIndividualCustomer	view	N/A
Sales.vSalesPerson	view	N/A
Sales.vStoreWithDemographics	view	N/A

Table: 'Person.StateProvince'

8 fields. 153 bytes.

State and province lookup table.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
StateProvinceID	int identity		int		Not null	Primary key for StateProvince records.
StateProvinceCode	nchar	3			Not null	ISO standard state or province code.
CountryRegionCode	nvarchar	3			Not null	ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlag	Flag		bit	(1)	Not null	0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
Name	Name		nvarchar(50)		Not null	State or province description.
TerritoryID	int				Not null	ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Person.StateProvince'

Primary Key Name	Field Name
PK_StateProvince_StateProvinceID	StateProvinceID

Foreign keys for table 'Person.StateProvince', 2 items

Foreign	Primary	Key Name
StateProvince.CountryRegionCode	CountryRegion.CountryRegionCode	FK_StateProvince_CountryRegion_CountryRegionCode
StateProvince.TerritoryID	SalesTerritory.TerritoryID	FK_StateProvince_SalesTerritory_TerritoryID

Indexes for table 'Person.StateProvince', 4 items

Index Name	Description	Clustered	Unique	Fields
PK_StateProvince_StateProvinceID	Clustered index created by a primary key constraint.	Yes	Yes	StateProvinceID
AK_StateProvince_Name	Unique nonclustered index.	No	Yes	Name
AK_StateProvince_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid
AK_StateProvince_StateProvinceCode_CountryRegionCode	Unique nonclustered index.	No	Yes	StateProvinceCode, CountryRegionCode

Triggers for table 'Person.StateProvince', 1 item

Name	Description	Type	Enabled
uStateProvince	AFTER UPDATE trigger setting the ModifiedDate column in the StateProvince table to the current date.	after Update	Yes

Dependencies for table 'Person.StateProvince', 7 items

Object Name	Type	Field Name
HumanResources.vEmployee	view	N/A
Person.uStateProvince	trigger	N/A
Person.vStateProvinceCountryRegion	view	N/A
Purchasing.vVendor	view	N/A
Sales.vIndividualCustomer	view	N/A
Sales.vSalesPerson	view	N/A
Sales.vStoreWithDemographics	view	N/A

Table: 'Production.BillOfMaterials'

9 fields. 78 bytes.

Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
BillOfMaterialsID	int identity		int		Not null	Primary key for BillOfMaterials records.
ProductAssemblyID	int				Null	Parent product identification number. Foreign key to Product.ProductID.
ComponentID	int				Not null	Component identification number. Foreign key to Product.ProductID.
StartDate	datetime			getdate()	Not null	Date the component started being used in the assembly item.
EndDate	datetime				Null	Date the component stopped being used in the assembly item.
UnitMeasureCode	nchar	3			Not null	Standard code identifying the unit of measure for the quantity.
BOMLevel	smallint				Not null	Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty	decimal	8,2		(1.00)	Not null	Quantity of the component needed to create the assembly.	([PerAssemblyQty]>=(1.00))
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.BillOfMaterials'

Primary Key Name	Field Name
PK_BillOfMaterials_BillOfMaterialsID	BillOfMaterialsID

Foreign keys for table 'Production.BillOfMaterials', 3 items

Foreign	Primary	Key Name
BillOfMaterials.ProductAssemblyID	Product.ProductID	FK_BillOfMaterials_Product_ProductAssemblyID
BillOfMaterials.ComponentID	Product.ProductID	FK_BillOfMaterials_Product_ComponentID
BillOfMaterials.UnitMeasureCode	UnitMeasure.UnitMeasureCode	FK_BillOfMaterials_UnitMeasure_UnitMeasureCode

Indexes for table 'Production.BillOfMaterials', 3 items

Index Name	Description	Clustered	Unique	Fields
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	Clustered index.	Yes	Yes	ProductAssemblyID, ComponentID, StartDate
PK_BillOfMaterials_BillOfMaterialsID	Nonclustered index created by a primary key constraint.	No	Yes	BillOfMaterialsID
IX_BillOfMaterials_UnitMeasureCode	Nonclustered index.	No	No	UnitMeasureCode

Triggers for table 'Production.BillOfMaterials', 1 item

Name	Description	Type	Enabled
uBillOfMaterials	AFTER UPDATE trigger setting the ModifiedDate column in the BillOfMaterials table to the current date.	after Update	Yes

Dependencies for table 'Production.BillOfMaterials', 7 items

Object Name	Type	Field Name
dbo.uspGetBillOfMaterials	stored procedure	N/A
dbo.uspGetWhereUsedProductID	stored procedure	N/A
Production.CK_BillOfMaterials_BOMLevel	check cns	N/A
Production.CK_BillOfMaterials_EndDate	check cns	N/A
Production.CK_BillOfMaterials_PerAssemblyQty	check cns	N/A
Production.CK_BillOfMaterials_ProductAssemblyID	check cns	N/A
Production.uBillOfMaterials	trigger	N/A

Table: 'Production.Culture'

3 fields. 128 bytes.

Lookup table containing the languages in which some AdventureWorks data is stored.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
CultureID	nchar	6			Not null	Primary key for Culture records.
Name	Name		nvarchar(50)		Not null	Culture description.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.Culture'

Primary Key Name	Field Name
PK_Culture_CultureID	CultureID

There are no Foreign Keys for this table.

Indexes for table 'Production.Culture', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_Culture_CultureID	Clustered index created by a primary key constraint.	Yes	Yes	CultureID
AK_Culture_Name	Unique nonclustered index.	No	Yes	Name

Triggers for table 'Production.Culture', 1 item

Name	Description	Type	Enabled
uCulture	AFTER UPDATE trigger setting the ModifiedDate column in the Culture table to the current date.	after Update	Yes

Dependencies for table 'Production.Culture', 1 item

Object Name	Type	Field Name
Production.uCulture	trigger	N/A

Table: 'Production.Document'

10 fields. 951 bytes.

Product maintenance documents.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
DocumentID	int identity		int		Not null	Primary key for Document records.
Title	nvarchar	50			Not null	Title of the document.
FileName	nvarchar	400			Not null	Directory path and file name of the document
FileExtension	nvarchar	8			Not null	File extension indicating the document type. For example, .doc or .txt.
Revision	nchar	5			Not null	Revision number of the document.
ChangeNumber	int			(0)	Not null	Engineering change approval number.
Status	tinyint				Not null	1 = Pending approval, 2 = Approved, 3 = Obsolete	([Status]>=(1) AND [Status]<=(3))
DocumentSummary	nvarchar	max			Null	Document abstract.
Document	varbinary				Null	Complete document.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.Document'

Primary Key Name	Field Name
PK_Document_DocumentID	DocumentID

There are no Foreign Keys for this table.

Indexes for table 'Production.Document', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_Document_DocumentID	Clustered index created by a primary key constraint.	Yes	Yes	DocumentID
AK_Document_FileName_Revision	Unique nonclustered index.	No	Yes	FileName, Revision

Triggers for table 'Production.Document', 1 item

Name	Description	Type	Enabled
uDocument	AFTER UPDATE trigger setting the ModifiedDate column in the Document table to the current date.	after Update	Yes

Dependencies for table 'Production.Document', 2 items

Object Name	Type	Field Name
Production.CK_Document_Status	check cns	N/A
Production.uDocument	trigger	N/A

Table: 'Production.Illustration'

3 fields. 20 bytes.

Bicycle assembly diagrams.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
IllustrationID	int identity		int		Not null	Primary key for Illustration records.
Diagram	xml				Null	Illustrations used in manufacturing instructions. Stored as XML.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.Illustration'

Primary Key Name	Field Name
PK_Illustration_IllustrationID	IllustrationID

There are no Foreign Keys for this table.

Indexes for table 'Production.Illustration', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_Illustration_IllustrationID	Clustered index created by a primary key constraint.	Yes	Yes	IllustrationID

Triggers for table 'Production.Illustration', 1 item

Name	Description	Type	Enabled
uIllustration	AFTER UPDATE trigger setting the ModifiedDate column in the Illustration table to the current date.	after Update	Yes

Dependencies for table 'Production.Illustration', 1 item

Object Name	Type	Field Name
Production.uIllustration	trigger	N/A

Table: 'Production.Location'

5 fields. 140 bytes.

Product inventory and manufacturing locations.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
LocationID	smallint identity		smallint		Not null	Primary key for Location records.
Name	Name		nvarchar(50)		Not null	Location description.
CostRate	smallmoney	10,4	decimal(10,4)	(0.00)	Not null	Standard hourly cost of the manufacturing location.	([CostRate]>=(0.00))
Availability	decimal	8,2		(0.00)	Not null	Work capacity (in hours) of the manufacturing location.	([Availability]>=(0.00))
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.Location'

Primary Key Name	Field Name
PK_Location_LocationID	LocationID

There are no Foreign Keys for this table.

Indexes for table 'Production.Location', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_Location_LocationID	Clustered index created by a primary key constraint.	Yes	Yes	LocationID
AK_Location_Name	Unique nonclustered index.	No	Yes	Name

Triggers for table 'Production.Location', 1 item

Name	Description	Type	Enabled
uLocation	AFTER UPDATE trigger setting the ModifiedDate column in the Location table to the current date.	after Update	Yes

Dependencies for table 'Production.Location', 3 items

Object Name	Type	Field Name
Production.CK_Location_Availability	check cns	N/A
Production.CK_Location_CostRate	check cns	N/A
Production.uLocation	trigger	N/A

Table: 'Production.Product'

25 fields. 368 bytes.

Products sold or used in the manfacturing of sold products.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductID	int identity		int		Not null	Primary key for Product records.
Name	Name		nvarchar(50)		Not null	Name of the product.
ProductNumber	nvarchar	25			Not null	Unique product identification number.
MakeFlag	Flag		bit	(1)	Not null	0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag	Flag		bit	(1)	Not null	0 = Product is not a salable item. 1 = Product is salable.
Color	nvarchar	15			Null	Product color.
SafetyStockLevel	smallint				Not null	Minimum inventory quantity.	([SafetyStockLevel]>(0))
ReorderPoint	smallint				Not null	Inventory level that triggers a purchase order or work order.	([ReorderPoint]>(0))
StandardCost	money	19,4	decimal(19,4)		Not null	Standard cost of the product.	([StandardCost]>=(0.00))
ListPrice	money	19,4	decimal(19,4)		Not null	Selling price.	([ListPrice]>=(0.00))
Size	nvarchar	5			Null	Product size.
SizeUnitMeasureCode	nchar	3			Null	Unit of measure for Size column.
WeightUnitMeasureCode	nchar	3			Null	Unit of measure for Weight column.
Weight	decimal	8,2			Null	Product weight.	([Weight]>(0.00))
DaysToManufacture	int				Not null	Number of days required to manufacture the product.	([DaysToManufacture]>=(0))
ProductLine	nchar	2			Null	R = Road, M = Mountain, T = Touring, S = Standard	(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
Class	nchar	2			Null	H = High, M = Medium, L = Low	(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
Style	nchar	2			Null	W = Womens, M = Mens, U = Universal	(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
ProductSubcategoryID	int				Null	Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelID	int				Null	Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDate	datetime				Not null	Date the product was available for sale.
SellEndDate	datetime				Null	Date the product was no longer available for sale.
DiscontinuedDate	datetime				Null	Date the product was discontinued.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.Product'

Primary Key Name	Field Name
PK_Product_ProductID	ProductID

Foreign keys for table 'Production.Product', 4 items

Foreign	Primary	Key Name
Product.ProductModelID	ProductModel.ProductModelID	FK_Product_ProductModel_ProductModelID
Product.ProductSubcategoryID	ProductSubcategory.ProductSubcategoryID	FK_Product_ProductSubcategory_ProductSubcategoryID
Product.SizeUnitMeasureCode	UnitMeasure.UnitMeasureCode	FK_Product_UnitMeasure_SizeUnitMeasureCode
Product.WeightUnitMeasureCode	UnitMeasure.UnitMeasureCode	FK_Product_UnitMeasure_WeightUnitMeasureCode

Indexes for table 'Production.Product', 4 items

Index Name	Description	Clustered	Unique	Fields
PK_Product_ProductID	Clustered index created by a primary key constraint.	Yes	Yes	ProductID
AK_Product_Name	Unique nonclustered index.	No	Yes	Name
AK_Product_ProductNumber	Unique nonclustered index.	No	Yes	ProductNumber
AK_Product_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid

Triggers for table 'Production.Product', 1 item

Name	Description	Type	Enabled
uProduct	AFTER UPDATE trigger setting the ModifiedDate column in the Product table to the current date.	after Update	Yes

Dependencies for table 'Production.Product', 17 items

Object Name	Type	Field Name
dbo.ufnGetProductDealerPrice	scalar function	N/A
dbo.ufnGetProductListPrice	scalar function	N/A
dbo.ufnGetProductStandardCost	scalar function	N/A
dbo.uspGetBillOfMaterials	stored procedure	N/A
dbo.uspGetWhereUsedProductID	stored procedure	N/A
Production.CK_Product_Class	check cns	N/A
Production.CK_Product_DaysToManufacture	check cns	N/A
Production.CK_Product_ListPrice	check cns	N/A
Production.CK_Product_ProductLine	check cns	N/A
Production.CK_Product_ReorderPoint	check cns	N/A
Production.CK_Product_SafetyStockLevel	check cns	N/A
Production.CK_Product_SellEndDate	check cns	N/A
Production.CK_Product_StandardCost	check cns	N/A
Production.CK_Product_Style	check cns	N/A
Production.CK_Product_Weight	check cns	N/A
Production.uProduct	trigger	N/A
Production.vProductAndDescription	view	N/A

Table: 'Production.ProductCategory'

4 fields. 136 bytes.

High-level product categorization.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductCategoryID	int identity		int		Not null	Primary key for ProductCategory records.
Name	Name		nvarchar(50)		Not null	Category description.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductCategory'

Primary Key Name	Field Name
PK_ProductCategory_ProductCategoryID	ProductCategoryID

There are no Foreign Keys for this table.

Indexes for table 'Production.ProductCategory', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_ProductCategory_ProductCategoryID	Clustered index created by a primary key constraint.	Yes	Yes	ProductCategoryID
AK_ProductCategory_Name	Unique nonclustered index.	No	Yes	Name
AK_ProductCategory_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid

Triggers for table 'Production.ProductCategory', 1 item

Name	Description	Type	Enabled
uProductCategory	AFTER UPDATE trigger setting the ModifiedDate column in the ProductCategory table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductCategory', 1 item

Object Name	Type	Field Name
Production.uProductCategory	trigger	N/A

Table: 'Production.ProductCostHistory'

5 fields. 73 bytes.

Changes in the cost of a product over time.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID
StartDate	datetime				Not null	Product cost start date.
EndDate	datetime				Null	Product cost end date.
StandardCost	money	19,4	decimal(19,4)		Not null	Standard cost of the product.	([StandardCost]>=(0.00))
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductCostHistory'

Primary Key Name	Field Names
PK_ProductCostHistory_ProductID_StartDate	ProductID, StartDate

Foreign keys for table 'Production.ProductCostHistory', 1 item

Foreign	Primary	Key Name
ProductCostHistory.ProductID	Product.ProductID	FK_ProductCostHistory_Product_ProductID

Indexes for table 'Production.ProductCostHistory', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductCostHistory_ProductID_StartDate	Clustered index created by a primary key constraint.	Yes	Yes	ProductID, StartDate

Triggers for table 'Production.ProductCostHistory', 1 item

Name	Description	Type	Enabled
uProductCostHistory	AFTER UPDATE trigger setting the ModifiedDate column in the ProductCostHistory table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductCostHistory', 4 items

Object Name	Type	Field Name
dbo.ufnGetProductStandardCost	scalar function	N/A
Production.CK_ProductCostHistory_EndDate	check cns	N/A
Production.CK_ProductCostHistory_StandardCost	check cns	N/A
Production.uProductCostHistory	trigger	N/A

Table: 'Production.ProductDescription'

4 fields. 836 bytes.

Product descriptions in several languages.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductDescriptionID	int identity		int		Not null	Primary key for ProductDescription records.
Description	nvarchar	400			Not null	Description of the product.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductDescription'

Primary Key Name	Field Name
PK_ProductDescription_ProductDescriptionID	ProductDescriptionID

There are no Foreign Keys for this table.

Indexes for table 'Production.ProductDescription', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_ProductDescription_ProductDescriptionID	Clustered index created by a primary key constraint.	Yes	Yes	ProductDescriptionID
AK_ProductDescription_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid

Triggers for table 'Production.ProductDescription', 1 item

Name	Description	Type	Enabled
uProductDescription	AFTER UPDATE trigger setting the ModifiedDate column in the ProductDescription table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductDescription', 2 items

Object Name	Type	Field Name
Production.uProductDescription	trigger	N/A
Production.vProductAndDescription	view	N/A

Table: 'Production.ProductDocument'

3 fields. 24 bytes.

Cross-reference table mapping products to related product documents.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID.
DocumentID	int				Not null	Document identification number. Foreign key to Document.DocumentID.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductDocument'

Primary Key Name	Field Names
PK_ProductDocument_ProductID_DocumentID	ProductID, DocumentID

Foreign keys for table 'Production.ProductDocument', 2 items

Foreign	Primary	Key Name
ProductDocument.DocumentID	Document.DocumentID	FK_ProductDocument_Document_DocumentID
ProductDocument.ProductID	Product.ProductID	FK_ProductDocument_Product_ProductID

Indexes for table 'Production.ProductDocument', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductDocument_ProductID_DocumentID	Clustered index created by a primary key constraint.	Yes	Yes	ProductID, DocumentID

Triggers for table 'Production.ProductDocument', 1 item

Name	Description	Type	Enabled
uProductDocument	AFTER UPDATE trigger setting the ModifiedDate column in the ProductDocument table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductDocument', 1 item

Object Name	Type	Field Name
Production.uProductDocument	trigger	N/A

Table: 'Production.ProductInventory'

7 fields. 61 bytes.

Product inventory information.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID.
LocationID	smallint				Not null	Inventory location identification number. Foreign key to Location.LocationID.
Shelf	nvarchar	10			Not null	Storage compartment within an inventory location.	([Shelf] like '[A-Za-z]' OR [Shelf]='N/A')
Bin	tinyint				Not null	Storage container on a shelf in an inventory location.	([Bin]>=(0) AND [Bin]<=(100))
Quantity	smallint			(0)	Not null	Quantity of products in the inventory location.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductInventory'

Primary Key Name	Field Names
PK_ProductInventory_ProductID_LocationID	ProductID, LocationID

Foreign keys for table 'Production.ProductInventory', 2 items

Foreign	Primary	Key Name
ProductInventory.LocationID	Location.LocationID	FK_ProductInventory_Location_LocationID
ProductInventory.ProductID	Product.ProductID	FK_ProductInventory_Product_ProductID

Indexes for table 'Production.ProductInventory', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductInventory_ProductID_LocationID	Clustered index created by a primary key constraint.	Yes	Yes	ProductID, LocationID

Triggers for table 'Production.ProductInventory', 1 item

Name	Description	Type	Enabled
uProductInventory	AFTER UPDATE trigger setting the ModifiedDate column in the ProductInventory table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductInventory', 4 items

Object Name	Type	Field Name
dbo.ufnGetStock	scalar function	N/A
Production.CK_ProductInventory_Bin	check cns	N/A
Production.CK_ProductInventory_Shelf	check cns	N/A
Production.uProductInventory	trigger	N/A

Table: 'Production.ProductListPriceHistory'

5 fields. 73 bytes.

Changes in the list price of a product over time.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID
StartDate	datetime				Not null	List price start date.
EndDate	datetime				Null	List price end date
ListPrice	money	19,4	decimal(19,4)		Not null	Product list price.	([ListPrice]>(0.00))
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductListPriceHistory'

Primary Key Name	Field Names
PK_ProductListPriceHistory_ProductID_StartDate	ProductID, StartDate

Foreign keys for table 'Production.ProductListPriceHistory', 1 item

Foreign	Primary	Key Name
ProductListPriceHistory.ProductID	Product.ProductID	FK_ProductListPriceHistory_Product_ProductID

Indexes for table 'Production.ProductListPriceHistory', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductListPriceHistory_ProductID_StartDate	Clustered index created by a primary key constraint.	Yes	Yes	ProductID, StartDate

Triggers for table 'Production.ProductListPriceHistory', 1 item

Name	Description	Type	Enabled
uProductListPriceHistory	AFTER UPDATE trigger setting the ModifiedDate column in the ProductListPriceHistory table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductListPriceHistory', 5 items

Object Name	Type	Field Name
dbo.ufnGetProductDealerPrice	scalar function	N/A
dbo.ufnGetProductListPrice	scalar function	N/A
Production.CK_ProductListPriceHistory_EndDate	check cns	N/A
Production.CK_ProductListPriceHistory_ListPrice	check cns	N/A
Production.uProductListPriceHistory	trigger	N/A

Table: 'Production.ProductModel'

6 fields. 136 bytes.

Product model classification.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductModelID	int identity		int		Not null	Primary key for ProductModel records.
Name	Name		nvarchar(50)		Not null	Product model description.
CatalogDescription	xml				Null	Detailed product catalog information in xml format.
Instructions	xml				Null	Manufacturing instructions in xml format.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductModel'

Primary Key Name	Field Name
PK_ProductModel_ProductModelID	ProductModelID

There are no Foreign Keys for this table.

Indexes for table 'Production.ProductModel', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_ProductModel_ProductModelID	Clustered index created by a primary key constraint.	Yes	Yes	ProductModelID
AK_ProductModel_Name	Unique nonclustered index.	No	Yes	Name
AK_ProductModel_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid

Triggers for table 'Production.ProductModel', 1 item

Name	Description	Type	Enabled
uProductModel	AFTER UPDATE trigger setting the ModifiedDate column in the ProductModel table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductModel', 4 items

Object Name	Type	Field Name
Production.uProductModel	trigger	N/A
Production.vProductAndDescription	view	N/A
Production.vProductModelCatalogDescription	view	N/A
Production.vProductModelInstructions	view	N/A

Table: 'Production.ProductModelIllustration'

3 fields. 24 bytes.

Cross-reference table mapping product models and illustrations.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductModelID	int				Not null	Primary key. Foreign key to ProductModel.ProductModelID.
IllustrationID	int				Not null	Primary key. Foreign key to Illustration.IllustrationID.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductModelIllustration'

Primary Key Name	Field Names
PK_ProductModelIllustration_ProductModelID_IllustrationID	ProductModelID, IllustrationID

Foreign keys for table 'Production.ProductModelIllustration', 2 items

Foreign	Primary	Key Name
ProductModelIllustration.IllustrationID	Illustration.IllustrationID	FK_ProductModelIllustration_Illustration_IllustrationID
ProductModelIllustration.ProductModelID	ProductModel.ProductModelID	FK_ProductModelIllustration_ProductModel_ProductModelID

Indexes for table 'Production.ProductModelIllustration', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductModelIllustration_ProductModelID_IllustrationID	Clustered index created by a primary key constraint.	Yes	Yes	ProductModelID, IllustrationID

Triggers for table 'Production.ProductModelIllustration', 1 item

Name	Description	Type	Enabled
uProductModelIllustration	AFTER UPDATE trigger setting the ModifiedDate column in the ProductModelIllustration table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductModelIllustration', 1 item

Object Name	Type	Field Name
Production.uProductModelIllustration	trigger	N/A

Table: 'Production.ProductModelProductDescriptionCulture'

4 fields. 36 bytes.

Cross-reference table mapping product descriptions and the language the description is written in.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductModelID	int				Not null	Primary key. Foreign key to ProductModel.ProductModelID.
ProductDescriptionID	int				Not null	Primary key. Foreign key to ProductDescription.ProductDescriptionID.
CultureID	nchar	6			Not null	Culture identification number. Foreign key to Culture.CultureID.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductModelProductDescriptionCulture'

Primary Key Name	Field Names
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ProductModelID, ProductDescriptionID, CultureID

Foreign keys for table 'Production.ProductModelProductDescriptionCulture', 3 items

Foreign	Primary	Key Name
ProductModelProductDescriptionCulture.CultureID	Culture.CultureID	FK_ProductModelProductDescriptionCulture_Culture_CultureID
ProductModelProductDescriptionCulture.ProductDescriptionID	ProductDescription.ProductDescriptionID	FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID
ProductModelProductDescriptionCulture.ProductModelID	ProductModel.ProductModelID	FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID

Indexes for table 'Production.ProductModelProductDescriptionCulture', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	Clustered index created by a primary key constraint.	Yes	Yes	ProductModelID, ProductDescriptionID, CultureID

Triggers for table 'Production.ProductModelProductDescriptionCulture', 1 item

Name	Description	Type	Enabled
uProductModelProductDescriptionCulture	AFTER UPDATE trigger setting the ModifiedDate column in the ProductModelProductDescriptionCulture table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductModelProductDescriptionCulture', 2 items

Object Name	Type	Field Name
Production.uProductModelProductDescriptionCulture	trigger	N/A
Production.vProductAndDescription	view	N/A

Table: 'Production.ProductPhoto'

6 fields. 220 bytes.

Product images.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductPhotoID	int identity		int		Not null	Primary key for ProductPhoto records.
ThumbNailPhoto	varbinary				Null	Small image of the product.
ThumbnailPhotoFileName	nvarchar	50			Null	Small image file name.
LargePhoto	varbinary				Null	Large image of the product.
LargePhotoFileName	nvarchar	50			Null	Large image file name.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductPhoto'

Primary Key Name	Field Name
PK_ProductPhoto_ProductPhotoID	ProductPhotoID

There are no Foreign Keys for this table.

Indexes for table 'Production.ProductPhoto', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductPhoto_ProductPhotoID	Clustered index created by a primary key constraint.	Yes	Yes	ProductPhotoID

Triggers for table 'Production.ProductPhoto', 1 item

Name	Description	Type	Enabled
uProductPhoto	AFTER UPDATE trigger setting the ModifiedDate column in the ProductPhoto table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductPhoto', 1 item

Object Name	Type	Field Name
Production.uProductPhoto	trigger	N/A

Table: 'Production.ProductProductPhoto'

4 fields. 25 bytes.

Cross-reference table mapping products and product photos.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID.
ProductPhotoID	int				Not null	Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
Primary	Flag		bit	(0)	Not null	0 = Photo is not the principal image. 1 = Photo is the principal image.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductProductPhoto'

Primary Key Name	Field Names
PK_ProductProductPhoto_ProductID_ProductPhotoID	ProductID, ProductPhotoID

Foreign keys for table 'Production.ProductProductPhoto', 2 items

Foreign	Primary	Key Name
ProductProductPhoto.ProductID	Product.ProductID	FK_ProductProductPhoto_Product_ProductID
ProductProductPhoto.ProductPhotoID	ProductPhoto.ProductPhotoID	FK_ProductProductPhoto_ProductPhoto_ProductPhotoID

Indexes for table 'Production.ProductProductPhoto', 1 item

Index Name	Description	Clustered	Unique	Fields
PK_ProductProductPhoto_ProductID_ProductPhotoID	Nonclustered index created by a primary key constraint.	No	Yes	ProductID, ProductPhotoID

Triggers for table 'Production.ProductProductPhoto', 1 item

Name	Description	Type	Enabled
uProductProductPhoto	AFTER UPDATE trigger setting the ModifiedDate column in the ProductProductPhoto table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductProductPhoto', 1 item

Object Name	Type	Field Name
Production.uProductProductPhoto	trigger	N/A

Table: 'Production.ProductReview'

8 fields. 7944 bytes.

Customer reviews of products they have purchased.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductReviewID	int identity		int		Not null	Primary key for ProductReview records.
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID.
ReviewerName	Name		nvarchar(50)		Not null	Name of the reviewer.
ReviewDate	datetime			getdate()	Not null	Date review was submitted.
EmailAddress	nvarchar	50			Not null	Reviewer's e-mail address.
Rating	int				Not null	Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.	([Rating]>=(1) AND [Rating]<=(5))
Comments	nvarchar	3850			Null	Reviewer's comments
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductReview'

Primary Key Name	Field Name
PK_ProductReview_ProductReviewID	ProductReviewID

Foreign keys for table 'Production.ProductReview', 1 item

Foreign	Primary	Key Name
ProductReview.ProductID	Product.ProductID	FK_ProductReview_Product_ProductID

Indexes for table 'Production.ProductReview', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_ProductReview_ProductReviewID	Clustered index created by a primary key constraint.	Yes	Yes	ProductReviewID
IX_ProductReview_ProductID_Name	Nonclustered index.	No	No	ProductID, ReviewerName

Triggers for table 'Production.ProductReview', 1 item

Name	Description	Type	Enabled
uProductReview	AFTER UPDATE trigger setting the ModifiedDate column in the ProductReview table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductReview', 2 items

Object Name	Type	Field Name
Production.CK_ProductReview_Rating	check cns	N/A
Production.uProductReview	trigger	N/A

Table: 'Production.ProductSubcategory'

5 fields. 140 bytes.

Product subcategories. See ProductCategory table.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ProductSubcategoryID	int identity		int		Not null	Primary key for ProductSubcategory records.
ProductCategoryID	int				Not null	Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
Name	Name		nvarchar(50)		Not null	Subcategory description.
rowguid	uniqueidentifier			newid()	Not null	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ProductSubcategory'

Primary Key Name	Field Name
PK_ProductSubcategory_ProductSubcategoryID	ProductSubcategoryID

Foreign keys for table 'Production.ProductSubcategory', 1 item

Foreign	Primary	Key Name
ProductSubcategory.ProductCategoryID	ProductCategory.ProductCategoryID	FK_ProductSubcategory_ProductCategory_ProductCategoryID

Indexes for table 'Production.ProductSubcategory', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_ProductSubcategory_ProductSubcategoryID	Clustered index created by a primary key constraint.	Yes	Yes	ProductSubcategoryID
AK_ProductSubcategory_Name	Unique nonclustered index.	No	Yes	Name
AK_ProductSubcategory_rowguid	Unique nonclustered index. Used to support replication samples.	No	Yes	rowguid

Triggers for table 'Production.ProductSubcategory', 1 item

Name	Description	Type	Enabled
uProductSubcategory	AFTER UPDATE trigger setting the ModifiedDate column in the ProductSubcategory table to the current date.	after Update	Yes

Dependencies for table 'Production.ProductSubcategory', 1 item

Object Name	Type	Field Name
Production.uProductSubcategory	trigger	N/A

Table: 'Production.ScrapReason'

3 fields. 118 bytes.

Manufacturing failure reasons lookup table.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
ScrapReasonID	smallint identity		smallint		Not null	Primary key for ScrapReason records.
Name	Name		nvarchar(50)		Not null	Failure description.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.ScrapReason'

Primary Key Name	Field Name
PK_ScrapReason_ScrapReasonID	ScrapReasonID

There are no Foreign Keys for this table.

Indexes for table 'Production.ScrapReason', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_ScrapReason_ScrapReasonID	Clustered index created by a primary key constraint.	Yes	Yes	ScrapReasonID
AK_ScrapReason_Name	Unique nonclustered index.	No	Yes	Name

Triggers for table 'Production.ScrapReason', 1 item

Name	Description	Type	Enabled
uScrapReason	AFTER UPDATE trigger setting the ModifiedDate column in the ScrapReason table to the current date.	after Update	Yes

Dependencies for table 'Production.ScrapReason', 1 item

Object Name	Type	Field Name
Production.uScrapReason	trigger	N/A

Table: 'Production.TransactionHistory'

9 fields. 75 bytes.

Record of each purchase order, sales order, or work order transaction year to date.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
TransactionID	int identity		int		Not null	Primary key for TransactionHistory records.
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID	int				Not null	Purchase order, sales order, or work order identification number.
ReferenceOrderLineID	int			(0)	Not null	Line number associated with the purchase order, sales order, or work order.
TransactionDate	datetime			getdate()	Not null	Date and time of the transaction.
TransactionType	nchar	1			Not null	W = WorkOrder, S = SalesOrder, P = PurchaseOrder	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')
Quantity	int				Not null	Product quantity.
ActualCost	money	19,4	decimal(19,4)		Not null	Product cost.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.TransactionHistory'

Primary Key Name	Field Name
PK_TransactionHistory_TransactionID	TransactionID

Foreign keys for table 'Production.TransactionHistory', 1 item

Foreign	Primary	Key Name
TransactionHistory.ProductID	Product.ProductID	FK_TransactionHistory_Product_ProductID

Indexes for table 'Production.TransactionHistory', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_TransactionHistory_TransactionID	Clustered index created by a primary key constraint.	Yes	Yes	TransactionID
IX_TransactionHistory_ProductID	Nonclustered index.	No	No	ProductID
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	Nonclustered index.	No	No	ReferenceOrderID, ReferenceOrderLineID

Triggers for table 'Production.TransactionHistory', 1 item

Name	Description	Type	Enabled
uTransactionHistory	AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistory table to the current date.	after Update	Yes

Dependencies for table 'Production.TransactionHistory', 7 items

Object Name	Type	Field Name
Production.CK_TransactionHistory_TransactionType	check cns	N/A
Production.iWorkOrder	trigger	N/A
Production.uTransactionHistory	trigger	N/A
Production.uWorkOrder	trigger	N/A
Purchasing.iPurchaseOrderDetail	trigger	N/A
Purchasing.uPurchaseOrderDetail	trigger	N/A
Sales.iduSalesOrderDetail	trigger	N/A

Table: 'Production.TransactionHistoryArchive'

9 fields. 75 bytes.

Transactions for previous years.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
TransactionID	int				Not null	Primary key for TransactionHistoryArchive records.
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID	int				Not null	Purchase order, sales order, or work order identification number.
ReferenceOrderLineID	int			(0)	Not null	Line number associated with the purchase order, sales order, or work order.
TransactionDate	datetime			getdate()	Not null	Date and time of the transaction.
TransactionType	nchar	1			Not null	W = Work Order, S = Sales Order, P = Purchase Order	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')
Quantity	int				Not null	Product quantity.
ActualCost	money	19,4	decimal(19,4)		Not null	Product cost.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.TransactionHistoryArchive'

Primary Key Name	Field Name
PK_TransactionHistoryArchive_TransactionID	TransactionID

There are no Foreign Keys for this table.

Indexes for table 'Production.TransactionHistoryArchive', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_TransactionHistoryArchive_TransactionID	Clustered index created by a primary key constraint.	Yes	Yes	TransactionID
IX_TransactionHistoryArchive_ProductID	Nonclustered index.	No	No	ProductID
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	Nonclustered index.	No	No	ReferenceOrderID, ReferenceOrderLineID

Triggers for table 'Production.TransactionHistoryArchive', 1 item

Name	Description	Type	Enabled
uTransactionHistoryArchive	AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistoryArchive table to the current date.	after Update	Yes

Dependencies for table 'Production.TransactionHistoryArchive', 2 items

Object Name	Type	Field Name
Production.CK_TransactionHistoryArchive_TransactionType	check cns	N/A
Production.uTransactionHistoryArchive	trigger	N/A

Table: 'Production.UnitMeasure'

3 fields. 122 bytes.

Unit of measure lookup table.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
UnitMeasureCode	nchar	3			Not null	Primary key.
Name	Name		nvarchar(50)		Not null	Unit of measure description.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.UnitMeasure'

Primary Key Name	Field Name
PK_UnitMeasure_UnitMeasureCode	UnitMeasureCode

There are no Foreign Keys for this table.

Indexes for table 'Production.UnitMeasure', 2 items

Index Name	Description	Clustered	Unique	Fields
PK_UnitMeasure_UnitMeasureCode	Clustered index created by a primary key constraint.	Yes	Yes	UnitMeasureCode
AK_UnitMeasure_Name	Unique nonclustered index.	No	Yes	Name

Triggers for table 'Production.UnitMeasure', 1 item

Name	Description	Type	Enabled
uUnitMeasure	AFTER UPDATE trigger setting the ModifiedDate column in the UnitMeasure table to the current date.	after Update	Yes

Dependencies for table 'Production.UnitMeasure', 1 item

Object Name	Type	Field Name
Production.uUnitMeasure	trigger	N/A

Table: 'Production.WorkOrder'

10 fields. 84 bytes.

Manufacturing work orders.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
WorkOrderID	int identity		int		Not null	Primary key for WorkOrder records.
ProductID	int				Not null	Product identification number. Foreign key to Product.ProductID.
OrderQty	int				Not null	Product quantity to build.	([OrderQty]>(0))
StockedQty	int				Not null	Quantity built and put in inventory.
ScrappedQty	smallint				Not null	Quantity that failed inspection.	([ScrappedQty]>=(0))
StartDate	datetime				Not null	Work order start date.
EndDate	datetime				Null	Work order end date.
DueDate	datetime				Not null	Work order due date.
ScrapReasonID	smallint				Null	Reason for inspection failure.
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.WorkOrder'

Primary Key Name	Field Name
PK_WorkOrder_WorkOrderID	WorkOrderID

Foreign keys for table 'Production.WorkOrder', 2 items

Foreign	Primary	Key Name
WorkOrder.ProductID	Product.ProductID	FK_WorkOrder_Product_ProductID
WorkOrder.ScrapReasonID	ScrapReason.ScrapReasonID	FK_WorkOrder_ScrapReason_ScrapReasonID

Indexes for table 'Production.WorkOrder', 3 items

Index Name	Description	Clustered	Unique	Fields
PK_WorkOrder_WorkOrderID	Clustered index created by a primary key constraint.	Yes	Yes	WorkOrderID
IX_WorkOrder_ProductID	Nonclustered index.	No	No	ProductID
IX_WorkOrder_ScrapReasonID	Nonclustered index.	No	No	ScrapReasonID

Triggers for table 'Production.WorkOrder', 2 items

Name	Description	Type	Enabled
iWorkOrder	AFTER INSERT trigger that inserts a row in the TransactionHistory table.	after Insert	Yes
uWorkOrder	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.	after Update	Yes

Dependencies for table 'Production.WorkOrder', 2 items

Object Name	Type	Field Name
Production.WorkOrder	user table	OrderQty
Production.WorkOrder	user table	ScrappedQty

Table: 'Production.WorkOrderRouting'

12 fields. 145 bytes.

Work order details.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
WorkOrderID	int				Not null	Primary key. Foreign key to WorkOrder.WorkOrderID.
ProductID	int				Not null	Primary key. Foreign key to Product.ProductID.
OperationSequence	smallint				Not null	Primary key. Indicates the manufacturing process sequence.
LocationID	smallint				Not null	Manufacturing location where the part is processed. Foreign key to Location.LocationID.
ScheduledStartDate	datetime				Not null	Planned manufacturing start date.
ScheduledEndDate	datetime				Not null	Planned manufacturing end date.
ActualStartDate	datetime				Null	Actual start date.
ActualEndDate	datetime				Null	Actual end date.
ActualResourceHrs	decimal	9,4			Null	Number of manufacturing hours used.	([ActualResourceHrs]>=(0.0000))
PlannedCost	money	19,4	decimal(19,4)		Not null	Estimated manufacturing cost.	([PlannedCost]>(0.00))
ActualCost	money	19,4	decimal(19,4)		Null	Actual manufacturing cost.	([ActualCost]>(0.00))
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.