Project adventureworks
Version mie55
Version Date 2009-01-03

Tables
Address (Person)
Column Data Type Nullable Default PK / Index
AddressID int not null PK PK_Address_AddressID
AddressLine1 nvarchar(60) not null index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
AddressLine2 nvarchar(60) null index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
City nvarchar(30) not null index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
StateProvinceID int not null index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
index IX_Address_StateProvinceID
PostalCode nvarchar(15) not null index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
rowguid uniqueidentifier not null (newid()) index AK_Address_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
StateProvince (Person) StateProvinceID StateProvinceID
Detail Table Column Referencing Column
CustomerAddress (Sales) AddressID AddressID
EmployeeAddress (HumanResources) AddressID AddressID
SalesOrderHeader (Sales) AddressID BillToAddressID
SalesOrderHeader (Sales) AddressID ShipToAddressID
VendorAddress (Purchasing) AddressID AddressID
Triggers
AddressType (Person)
Column Data Type Nullable Default PK / Index
AddressTypeID int not null PK PK_AddressType_AddressTypeID
Name dbo.Name not null index AK_AddressType_Name
rowguid uniqueidentifier not null (newid()) index AK_AddressType_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
CustomerAddress (Sales) AddressTypeID AddressTypeID
VendorAddress (Purchasing) AddressTypeID AddressTypeID
Triggers
AWBuildVersion (dbo)
Column Data Type Nullable Default PK / Index
SystemInformationID tinyint not null PK PK_AWBuildVersion_SystemInformationID
Database Version nvarchar(25) not null
VersionDate datetime not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
BillOfMaterials (Production)
Column Data Type Nullable Default PK / Index
BillOfMaterialsID int not null PK PK_BillOfMaterials_BillOfMaterialsID
ProductAssemblyID int null index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ComponentID int not null index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
StartDate datetime not null (getdate()) index AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
EndDate datetime null
UnitMeasureCode nchar(3) not null index IX_BillOfMaterials_UnitMeasureCode
BOMLevel smallint not null
PerAssemblyQty decimal(8, 2) not null ((1.00))
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ComponentID ProductID
Product (Production) ProductAssemblyID ProductID
UnitMeasure (Production) UnitMeasureCode UnitMeasureCode
Detail Table Column Referencing Column
Triggers
Contact (Person)
Column Data Type Nullable Default PK / Index
ContactID int not null PK PK_Contact_ContactID
NameStyle dbo.NameStyle not null ((0))
Title nvarchar(8) null
FirstName dbo.Name not null
MiddleName dbo.Name null
LastName dbo.Name not null
Suffix nvarchar(10) null
EmailAddress nvarchar(50) null index IX_Contact_EmailAddress
EmailPromotion int not null ((0))
Phone dbo.Phone null
PasswordHash varchar(128) not null
PasswordSalt varchar(10) not null
AdditionalContactInfo xml null index PXML_Contact_AddContact
rowguid uniqueidentifier not null (newid()) index AK_Contact_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ContactCreditCard (Sales) ContactID ContactID
Employee (HumanResources) ContactID ContactID
Individual (Sales) ContactID ContactID
SalesOrderHeader (Sales) ContactID ContactID
StoreContact (Sales) ContactID ContactID
VendorContact (Purchasing) ContactID ContactID
Triggers
ContactCreditCard (Sales)
Column Data Type Nullable Default PK / Index
ContactID int not null PK PK_ContactCreditCard_ContactID_CreditCardID
CreditCardID int not null PK PK_ContactCreditCard_ContactID_CreditCardID
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
CreditCard (Sales) CreditCardID CreditCardID
Detail Table Column Referencing Column
Triggers
ContactType (Person)
Column Data Type Nullable Default PK / Index
ContactTypeID int not null PK PK_ContactType_ContactTypeID
Name dbo.Name not null index AK_ContactType_Name
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
StoreContact (Sales) ContactTypeID ContactTypeID
VendorContact (Purchasing) ContactTypeID ContactTypeID
Triggers
CountryRegion (Person)
Column Data Type Nullable Default PK / Index
CountryRegionCode nvarchar(3) not null PK PK_CountryRegion_CountryRegionCode
Name dbo.Name not null index AK_CountryRegion_Name
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
CountryRegionCurrency (Sales) CountryRegionCode CountryRegionCode
StateProvince (Person) CountryRegionCode CountryRegionCode
Triggers
CountryRegionCurrency (Sales)
Column Data Type Nullable Default PK / Index
CountryRegionCode nvarchar(3) not null PK PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode
CurrencyCode nchar(3) not null PK PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode
index IX_CountryRegionCurrency_CurrencyCode
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
CountryRegion (Person) CountryRegionCode CountryRegionCode
Currency (Sales) CurrencyCode CurrencyCode
Detail Table Column Referencing Column
Triggers
CreditCard (Sales)
Column Data Type Nullable Default PK / Index
CreditCardID int not null PK PK_CreditCard_CreditCardID
CardType nvarchar(50) not null
CardNumber nvarchar(25) not null index AK_CreditCard_CardNumber
ExpMonth tinyint not null
ExpYear smallint not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ContactCreditCard (Sales) CreditCardID CreditCardID
SalesOrderHeader (Sales) CreditCardID CreditCardID
Triggers
Culture (Production)
Column Data Type Nullable Default PK / Index
CultureID nchar(6) not null PK PK_Culture_CultureID
Name dbo.Name not null index AK_Culture_Name
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductModelProductDescriptionCulture (Production) CultureID CultureID
Triggers
Currency (Sales)
Column Data Type Nullable Default PK / Index
CurrencyCode nchar(3) not null PK PK_Currency_CurrencyCode
Name dbo.Name not null index AK_Currency_Name
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
CountryRegionCurrency (Sales) CurrencyCode CurrencyCode
CurrencyRate (Sales) CurrencyCode FromCurrencyCode
CurrencyRate (Sales) CurrencyCode ToCurrencyCode
Triggers
CurrencyRate (Sales)
Column Data Type Nullable Default PK / Index
CurrencyRateID int not null PK PK_CurrencyRate_CurrencyRateID
CurrencyRateDate datetime not null index AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
FromCurrencyCode nchar(3) not null index AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
ToCurrencyCode nchar(3) not null index AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
AverageRate money not null
EndOfDayRate money not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Currency (Sales) FromCurrencyCode CurrencyCode
Currency (Sales) ToCurrencyCode CurrencyCode
Detail Table Column Referencing Column
SalesOrderHeader (Sales) CurrencyRateID CurrencyRateID
Triggers
Customer (Sales)
Column Data Type Nullable Default PK / Index
CustomerID int not null PK PK_Customer_CustomerID
TerritoryID int null index IX_Customer_TerritoryID
AccountNumber index AK_Customer_AccountNumber
CustomerType nchar(1) not null
rowguid uniqueidentifier not null (newid()) index AK_Customer_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
SalesTerritory (Sales) TerritoryID TerritoryID
Detail Table Column Referencing Column
CustomerAddress (Sales) CustomerID CustomerID
Individual (Sales) CustomerID CustomerID
SalesOrderHeader (Sales) CustomerID CustomerID
Store (Sales) CustomerID CustomerID
Triggers
CustomerAddress (Sales)
Column Data Type Nullable Default PK / Index
CustomerID int not null PK PK_CustomerAddress_CustomerID_AddressID
AddressID int not null PK PK_CustomerAddress_CustomerID_AddressID
AddressTypeID int not null
rowguid uniqueidentifier not null (newid()) index AK_CustomerAddress_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Address (Person) AddressID AddressID
AddressType (Person) AddressTypeID AddressTypeID
Customer (Sales) CustomerID CustomerID
Detail Table Column Referencing Column
Triggers
DatabaseLog (dbo)
Column Data Type Nullable Default PK / Index
DatabaseLogID int not null PK PK_DatabaseLog_DatabaseLogID
PostTime datetime not null
DatabaseUser sysname not null
Event sysname not null
Schema sysname null
Object sysname null
TSQL nvarchar(max) not null
XmlEvent xml not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
Department (HumanResources)
Column Data Type Nullable Default PK / Index
DepartmentID smallint not null PK PK_Department_DepartmentID
Name dbo.Name not null index AK_Department_Name
GroupName dbo.Name not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
EmployeeDepartmentHistory (HumanResources) DepartmentID DepartmentID
Triggers
Document (Production)
Column Data Type Nullable Default PK / Index
DocumentID int not null PK PK_Document_DocumentID
Title nvarchar(50) not null
FileName nvarchar(400) not null index AK_Document_FileName_Revision
FileExtension nvarchar(8) not null
Revision nchar(5) not null index AK_Document_FileName_Revision
ChangeNumber int not null ((0))
Status tinyint not null
DocumentSummary nvarchar(max) null
Document varbinary(max) null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductDocument (Production) DocumentID DocumentID
Triggers
Employee (HumanResources)
Column Data Type Nullable Default PK / Index
EmployeeID int not null PK PK_Employee_EmployeeID
NationalIDNumber nvarchar(15) not null index AK_Employee_NationalIDNumber
ContactID int not null
LoginID nvarchar(256) not null index AK_Employee_LoginID
ManagerID int null index IX_Employee_ManagerID
Title nvarchar(50) not null
BirthDate datetime not null
MaritalStatus nchar(1) not null
Gender nchar(1) not null
HireDate datetime not null
SalariedFlag dbo.Flag not null ((1))
VacationHours smallint not null ((0))
SickLeaveHours smallint not null ((0))
CurrentFlag dbo.Flag not null ((1))
rowguid uniqueidentifier not null (newid()) index AK_Employee_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
Employee (HumanResources) ManagerID EmployeeID
Detail Table Column Referencing Column
Employee (HumanResources) EmployeeID ManagerID
EmployeeAddress (HumanResources) EmployeeID EmployeeID
EmployeeDepartmentHistory (HumanResources) EmployeeID EmployeeID
EmployeePayHistory (HumanResources) EmployeeID EmployeeID
JobCandidate (HumanResources) EmployeeID EmployeeID
PurchaseOrderHeader (Purchasing) EmployeeID EmployeeID
SalesPerson (Sales) EmployeeID SalesPersonID
Triggers
dEmployee INSTEAD OF DELETE
EmployeeAddress (HumanResources)
Column Data Type Nullable Default PK / Index
EmployeeID int not null PK PK_EmployeeAddress_EmployeeID_AddressID
AddressID int not null PK PK_EmployeeAddress_EmployeeID_AddressID
rowguid uniqueidentifier not null (newid()) index AK_EmployeeAddress_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Address (Person) AddressID AddressID
Employee (HumanResources) EmployeeID EmployeeID
Detail Table Column Referencing Column
Triggers
EmployeeDepartmentHistory (HumanResources)
Column Data Type Nullable Default PK / Index
EmployeeID int not null PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
DepartmentID smallint not null PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
index IX_EmployeeDepartmentHistory_DepartmentID
ShiftID tinyint not null PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
index IX_EmployeeDepartmentHistory_ShiftID
StartDate datetime not null PK PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
EndDate datetime null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Department (HumanResources) DepartmentID DepartmentID
Employee (HumanResources) EmployeeID EmployeeID
Shift (HumanResources) ShiftID ShiftID
Detail Table Column Referencing Column
Triggers
EmployeePayHistory (HumanResources)
Column Data Type Nullable Default PK / Index
EmployeeID int not null PK PK_EmployeePayHistory_EmployeeID_RateChangeDate
RateChangeDate datetime not null PK PK_EmployeePayHistory_EmployeeID_RateChangeDate
Rate money not null
PayFrequency tinyint not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Employee (HumanResources) EmployeeID EmployeeID
Detail Table Column Referencing Column
Triggers
ErrorLog (dbo)
Column Data Type Nullable Default PK / Index
ErrorLogID int not null PK PK_ErrorLog_ErrorLogID
ErrorTime datetime not null (getdate())
UserName sysname not null
ErrorNumber int not null
ErrorSeverity int null
ErrorState int null
ErrorProcedure nvarchar(126) null
ErrorLine int null
ErrorMessage nvarchar(4000) not null
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
Illustration (Production)
Column Data Type Nullable Default PK / Index
IllustrationID int not null PK PK_Illustration_IllustrationID
Diagram xml null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductModelIllustration (Production) IllustrationID IllustrationID
Triggers
Individual (Sales)
Column Data Type Nullable Default PK / Index
CustomerID int not null PK PK_Individual_CustomerID
ContactID int not null
Demographics xml null index PXML_Individual_Demographics
index XMLPATH_Individual_Demographics
index XMLPROPERTY_Individual_Demographics
index XMLVALUE_Individual_Demographics
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
Customer (Sales) CustomerID CustomerID
Detail Table Column Referencing Column
Triggers
iuIndividual ON INSERT UPDATE
JobCandidate (HumanResources)
Column Data Type Nullable Default PK / Index
JobCandidateID int not null PK PK_JobCandidate_JobCandidateID
EmployeeID int null index IX_JobCandidate_EmployeeID
Resume xml null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Employee (HumanResources) EmployeeID EmployeeID
Detail Table Column Referencing Column
Triggers
Location (Production)
Column Data Type Nullable Default PK / Index
LocationID smallint not null PK PK_Location_LocationID
Name dbo.Name not null index AK_Location_Name
CostRate smallmoney not null ((0.00))
Availability decimal(8, 2) not null ((0.00))
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductInventory (Production) LocationID LocationID
WorkOrderRouting (Production) LocationID LocationID
Triggers
Product (Production)
Column Data Type Nullable Default PK / Index
ProductID int not null PK PK_Product_ProductID
Name dbo.Name not null index AK_Product_Name
ProductNumber nvarchar(25) not null index AK_Product_ProductNumber
MakeFlag dbo.Flag not null ((1))
FinishedGoodsFlag dbo.Flag not null ((1))
Color nvarchar(15) null
SafetyStockLevel smallint not null
ReorderPoint smallint not null
StandardCost money not null
ListPrice money not null
Size nvarchar(5) null
SizeUnitMeasureCode nchar(3) null
WeightUnitMeasureCode nchar(3) null
Weight decimal(8, 2) null
DaysToManufacture int not null
ProductLine nchar(2) null
Class nchar(2) null
Style nchar(2) null
ProductSubcategoryID int null
ProductModelID int null
SellStartDate datetime not null
SellEndDate datetime null
DiscontinuedDate datetime null
rowguid uniqueidentifier not null (newid()) index AK_Product_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
ProductModel (Production) ProductModelID ProductModelID
ProductSubcategory (Production) ProductSubcategoryID ProductSubcategoryID
UnitMeasure (Production) SizeUnitMeasureCode UnitMeasureCode
UnitMeasure (Production) WeightUnitMeasureCode UnitMeasureCode
Detail Table Column Referencing Column
BillOfMaterials (Production) ProductID ComponentID
BillOfMaterials (Production) ProductID ProductAssemblyID
ProductCostHistory (Production) ProductID ProductID
ProductDocument (Production) ProductID ProductID
ProductInventory (Production) ProductID ProductID
ProductListPriceHistory (Production) ProductID ProductID
ProductProductPhoto (Production) ProductID ProductID
ProductReview (Production) ProductID ProductID
ProductVendor (Purchasing) ProductID ProductID
PurchaseOrderDetail (Purchasing) ProductID ProductID
ShoppingCartItem (Sales) ProductID ProductID
SpecialOfferProduct (Sales) ProductID ProductID
TransactionHistory (Production) ProductID ProductID
WorkOrder (Production) ProductID ProductID
Triggers
ProductCategory (Production)
Column Data Type Nullable Default PK / Index
ProductCategoryID int not null PK PK_ProductCategory_ProductCategoryID
Name dbo.Name not null index AK_ProductCategory_Name
rowguid uniqueidentifier not null (newid()) index AK_ProductCategory_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductSubcategory (Production) ProductCategoryID ProductCategoryID
Triggers
ProductCostHistory (Production)
Column Data Type Nullable Default PK / Index
ProductID int not null PK PK_ProductCostHistory_ProductID_StartDate
StartDate datetime not null PK PK_ProductCostHistory_ProductID_StartDate
EndDate datetime null
StandardCost money not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
Detail Table Column Referencing Column
Triggers
ProductDescription (Production)
Column Data Type Nullable Default PK / Index
ProductDescriptionID int not null PK PK_ProductDescription_ProductDescriptionID
Description nvarchar(400) not null
rowguid uniqueidentifier not null (newid()) index AK_ProductDescription_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductModelProductDescriptionCulture (Production) ProductDescriptionID ProductDescriptionID
Triggers
ProductDocument (Production)
Column Data Type Nullable Default PK / Index
ProductID int not null PK PK_ProductDocument_ProductID_DocumentID
DocumentID int not null PK PK_ProductDocument_ProductID_DocumentID
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Document (Production) DocumentID DocumentID
Product (Production) ProductID ProductID
Detail Table Column Referencing Column
Triggers
ProductInventory (Production)
Column Data Type Nullable Default PK / Index
ProductID int not null PK PK_ProductInventory_ProductID_LocationID
LocationID smallint not null PK PK_ProductInventory_ProductID_LocationID
Shelf nvarchar(10) not null
Bin tinyint not null
Quantity smallint not null ((0))
rowguid uniqueidentifier not null (newid())
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Location (Production) LocationID LocationID
Product (Production) ProductID ProductID
Detail Table Column Referencing Column
Triggers
ProductListPriceHistory (Production)
Column Data Type Nullable Default PK / Index
ProductID int not null PK PK_ProductListPriceHistory_ProductID_StartDate
StartDate datetime not null PK PK_ProductListPriceHistory_ProductID_StartDate
EndDate datetime null
ListPrice money not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
Detail Table Column Referencing Column
Triggers
ProductModel (Production)
Column Data Type Nullable Default PK / Index
ProductModelID int not null PK PK_ProductModel_ProductModelID
Name dbo.Name not null index AK_ProductModel_Name
CatalogDescription xml null index PXML_ProductModel_CatalogDescription
Instructions xml null index PXML_ProductModel_Instructions
rowguid uniqueidentifier not null (newid()) index AK_ProductModel_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Product (Production) ProductModelID ProductModelID
ProductModelIllustration (Production) ProductModelID ProductModelID
ProductModelProductDescriptionCulture (Production) ProductModelID ProductModelID
Triggers
ProductModelIllustration (Production)
Column Data Type Nullable Default PK / Index
ProductModelID int not null PK PK_ProductModelIllustration_ProductModelID_IllustrationID
IllustrationID int not null PK PK_ProductModelIllustration_ProductModelID_IllustrationID
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Illustration (Production) IllustrationID IllustrationID
ProductModel (Production) ProductModelID ProductModelID
Detail Table Column Referencing Column
Triggers
ProductModelProductDescriptionCulture (Production)
Column Data Type Nullable Default PK / Index
ProductModelID int not null PK PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
ProductDescriptionID int not null PK PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
CultureID nchar(6) not null PK PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Culture (Production) CultureID CultureID
ProductDescription (Production) ProductDescriptionID ProductDescriptionID
ProductModel (Production) ProductModelID ProductModelID
Detail Table Column Referencing Column
Triggers
ProductPhoto (Production)
Column Data Type Nullable Default PK / Index
ProductPhotoID int not null PK PK_ProductPhoto_ProductPhotoID
ThumbNailPhoto varbinary(max) null
ThumbnailPhotoFileName nvarchar(50) null
LargePhoto varbinary(max) null
LargePhotoFileName nvarchar(50) null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductProductPhoto (Production) ProductPhotoID ProductPhotoID
Triggers
ProductProductPhoto (Production)
Column Data Type Nullable Default PK / Index
ProductID int not null PK PK_ProductProductPhoto_ProductID_ProductPhotoID
ProductPhotoID int not null PK PK_ProductProductPhoto_ProductID_ProductPhotoID
Primary dbo.Flag not null ((0))
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
ProductPhoto (Production) ProductPhotoID ProductPhotoID
Detail Table Column Referencing Column
Triggers
ProductReview (Production)
Column Data Type Nullable Default PK / Index
ProductReviewID int not null PK PK_ProductReview_ProductReviewID
ProductID int not null index IX_ProductReview_ProductID_Name
ReviewerName dbo.Name not null index IX_ProductReview_ProductID_Name
ReviewDate datetime not null (getdate())
EmailAddress nvarchar(50) not null
Rating int not null
Comments nvarchar(3850) null index IX_ProductReview_ProductID_Name
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
Detail Table Column Referencing Column
Triggers
ProductSubcategory (Production)
Column Data Type Nullable Default PK / Index
ProductSubcategoryID int not null PK PK_ProductSubcategory_ProductSubcategoryID
ProductCategoryID int not null
Name dbo.Name not null index AK_ProductSubcategory_Name
rowguid uniqueidentifier not null (newid()) index AK_ProductSubcategory_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
ProductCategory (Production) ProductCategoryID ProductCategoryID
Detail Table Column Referencing Column
Product (Production) ProductSubcategoryID ProductSubcategoryID
Triggers
ProductVendor (Purchasing)
Column Data Type Nullable Default PK / Index
ProductID int not null PK PK_ProductVendor_ProductID_VendorID
VendorID int not null PK PK_ProductVendor_ProductID_VendorID
index IX_ProductVendor_VendorID
AverageLeadTime int not null
StandardPrice money not null
LastReceiptCost money null
LastReceiptDate datetime null
MinOrderQty int not null
MaxOrderQty int not null
OnOrderQty int null
UnitMeasureCode nchar(3) not null index IX_ProductVendor_UnitMeasureCode
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
UnitMeasure (Production) UnitMeasureCode UnitMeasureCode
Vendor (Purchasing) VendorID VendorID
Detail Table Column Referencing Column
Triggers
PurchaseOrderDetail (Purchasing)
Column Data Type Nullable Default PK / Index
PurchaseOrderID int not null PK PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
PurchaseOrderDetailID int not null PK PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
DueDate datetime not null
OrderQty smallint not null
ProductID int not null index IX_PurchaseOrderDetail_ProductID
UnitPrice money not null
LineTotal
ReceivedQty decimal(8, 2) not null
RejectedQty decimal(8, 2) not null
StockedQty
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
PurchaseOrderHeader (Purchasing) PurchaseOrderID PurchaseOrderID
Detail Table Column Referencing Column
Triggers
iPurchaseOrderDetail ON INSERT
uPurchaseOrderDetail ON UPDATE
PurchaseOrderHeader (Purchasing)
Column Data Type Nullable Default PK / Index
PurchaseOrderID int not null PK PK_PurchaseOrderHeader_PurchaseOrderID
RevisionNumber tinyint not null ((0))
Status tinyint not null ((1))
EmployeeID int not null index IX_PurchaseOrderHeader_EmployeeID
VendorID int not null index IX_PurchaseOrderHeader_VendorID
ShipMethodID int not null
OrderDate datetime not null (getdate())
ShipDate datetime null
SubTotal money not null ((0.00))
TaxAmt money not null ((0.00))
Freight money not null ((0.00))
TotalDue
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Employee (HumanResources) EmployeeID EmployeeID
ShipMethod (Purchasing) ShipMethodID ShipMethodID
Vendor (Purchasing) VendorID VendorID
Detail Table Column Referencing Column
PurchaseOrderDetail (Purchasing) PurchaseOrderID PurchaseOrderID
Triggers
uPurchaseOrderHeader ON UPDATE
SalesOrderDetail (Sales)
Column Data Type Nullable Default PK / Index
SalesOrderID int not null PK PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
SalesOrderDetailID int not null PK PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
CarrierTrackingNumber nvarchar(25) null
OrderQty smallint not null
ProductID int not null index IX_SalesOrderDetail_ProductID
SpecialOfferID int not null
UnitPrice money not null
UnitPriceDiscount money not null ((0.0))
LineTotal
rowguid uniqueidentifier not null (newid()) index AK_SalesOrderDetail_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
SalesOrderHeader (Sales) SalesOrderID SalesOrderID
SpecialOfferProduct (Sales) SpecialOfferID SpecialOfferID
Detail Table Column Referencing Column
Triggers
iduSalesOrderDetail ON INSERT UPDATE DELETE
SalesOrderHeader (Sales)
Column Data Type Nullable Default PK / Index
SalesOrderID int not null PK PK_SalesOrderHeader_SalesOrderID
RevisionNumber tinyint not null ((0))
OrderDate datetime not null (getdate())
DueDate datetime not null
ShipDate datetime null
Status tinyint not null ((1))
OnlineOrderFlag dbo.Flag not null ((1))
SalesOrderNumber index AK_SalesOrderHeader_SalesOrderNumber
PurchaseOrderNumber dbo.OrderNumber null
AccountNumber dbo.AccountNumber null
CustomerID int not null index IX_SalesOrderHeader_CustomerID
ContactID int not null
SalesPersonID int null index IX_SalesOrderHeader_SalesPersonID
TerritoryID int null
BillToAddressID int not null
ShipToAddressID int not null
ShipMethodID int not null
CreditCardID int null
CreditCardApprovalCode varchar(15) null
CurrencyRateID int null
SubTotal money not null ((0.00))
TaxAmt money not null ((0.00))
Freight money not null ((0.00))
TotalDue
Comment nvarchar(128) null
rowguid uniqueidentifier not null (newid()) index AK_SalesOrderHeader_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Address (Person) BillToAddressID AddressID
Address (Person) ShipToAddressID AddressID
Contact (Person) ContactID ContactID
CreditCard (Sales) CreditCardID CreditCardID
CurrencyRate (Sales) CurrencyRateID CurrencyRateID
Customer (Sales) CustomerID CustomerID
SalesPerson (Sales) SalesPersonID SalesPersonID
SalesTerritory (Sales) TerritoryID TerritoryID
ShipMethod (Purchasing) ShipMethodID ShipMethodID
Detail Table Column Referencing Column
SalesOrderDetail (Sales) SalesOrderID SalesOrderID
SalesOrderHeaderSalesReason (Sales) SalesOrderID SalesOrderID
Triggers
uSalesOrderHeader ON UPDATE
SalesOrderHeaderSalesReason (Sales)
Column Data Type Nullable Default PK / Index
SalesOrderID int not null PK PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
SalesReasonID int not null PK PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
SalesOrderHeader (Sales) SalesOrderID SalesOrderID
SalesReason (Sales) SalesReasonID SalesReasonID
Detail Table Column Referencing Column
Triggers
SalesPerson (Sales)
Column Data Type Nullable Default PK / Index
SalesPersonID int not null PK PK_SalesPerson_SalesPersonID
TerritoryID int null
SalesQuota money null
Bonus money not null ((0.00))
CommissionPct smallmoney not null ((0.00))
SalesYTD money not null ((0.00))
SalesLastYear money not null ((0.00))
rowguid uniqueidentifier not null (newid()) index AK_SalesPerson_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Employee (HumanResources) SalesPersonID EmployeeID
SalesTerritory (Sales) TerritoryID TerritoryID
Detail Table Column Referencing Column
SalesOrderHeader (Sales) SalesPersonID SalesPersonID
SalesPersonQuotaHistory (Sales) SalesPersonID SalesPersonID
SalesTerritoryHistory (Sales) SalesPersonID SalesPersonID
Store (Sales) SalesPersonID SalesPersonID
Triggers
SalesPersonQuotaHistory (Sales)
Column Data Type Nullable Default PK / Index
SalesPersonID int not null PK PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate
QuotaDate datetime not null PK PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate
SalesQuota money not null
rowguid uniqueidentifier not null (newid()) index AK_SalesPersonQuotaHistory_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
SalesPerson (Sales) SalesPersonID SalesPersonID
Detail Table Column Referencing Column
Triggers
SalesReason (Sales)
Column Data Type Nullable Default PK / Index
SalesReasonID int not null PK PK_SalesReason_SalesReasonID
Name dbo.Name not null
ReasonType dbo.Name not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
SalesOrderHeaderSalesReason (Sales) SalesReasonID SalesReasonID
Triggers
SalesTaxRate (Sales)
Column Data Type Nullable Default PK / Index
SalesTaxRateID int not null PK PK_SalesTaxRate_SalesTaxRateID
StateProvinceID int not null index AK_SalesTaxRate_StateProvinceID_TaxType
TaxType tinyint not null index AK_SalesTaxRate_StateProvinceID_TaxType
TaxRate smallmoney not null ((0.00))
Name dbo.Name not null
rowguid uniqueidentifier not null (newid()) index AK_SalesTaxRate_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
StateProvince (Person) StateProvinceID StateProvinceID
Detail Table Column Referencing Column
Triggers
SalesTerritory (Sales)
Column Data Type Nullable Default PK / Index
TerritoryID int not null PK PK_SalesTerritory_TerritoryID
Name dbo.Name not null index AK_SalesTerritory_Name
CountryRegionCode nvarchar(3) not null
Group nvarchar(50) not null
SalesYTD money not null ((0.00))
SalesLastYear money not null ((0.00))
CostYTD money not null ((0.00))
CostLastYear money not null ((0.00))
rowguid uniqueidentifier not null (newid()) index AK_SalesTerritory_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Customer (Sales) TerritoryID TerritoryID
SalesOrderHeader (Sales) TerritoryID TerritoryID
SalesPerson (Sales) TerritoryID TerritoryID
SalesTerritoryHistory (Sales) TerritoryID TerritoryID
StateProvince (Person) TerritoryID TerritoryID
Triggers
SalesTerritoryHistory (Sales)
Column Data Type Nullable Default PK / Index
SalesPersonID int not null PK PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID
TerritoryID int not null PK PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID
StartDate datetime not null PK PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID
EndDate datetime null
rowguid uniqueidentifier not null (newid()) index AK_SalesTerritoryHistory_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
SalesPerson (Sales) SalesPersonID SalesPersonID
SalesTerritory (Sales) TerritoryID TerritoryID
Detail Table Column Referencing Column
Triggers
ScrapReason (Production)
Column Data Type Nullable Default PK / Index
ScrapReasonID smallint not null PK PK_ScrapReason_ScrapReasonID
Name dbo.Name not null index AK_ScrapReason_Name
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
WorkOrder (Production) ScrapReasonID ScrapReasonID
Triggers
Shift (HumanResources)
Column Data Type Nullable Default PK / Index
ShiftID tinyint not null PK PK_Shift_ShiftID
Name dbo.Name not null index AK_Shift_Name
StartTime datetime not null index AK_Shift_StartTime_EndTime
EndTime datetime not null index AK_Shift_StartTime_EndTime
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
EmployeeDepartmentHistory (HumanResources) ShiftID ShiftID
Triggers
ShipMethod (Purchasing)
Column Data Type Nullable Default PK / Index
ShipMethodID int not null PK PK_ShipMethod_ShipMethodID
Name dbo.Name not null index AK_ShipMethod_Name
ShipBase money not null ((0.00))
ShipRate money not null ((0.00))
rowguid uniqueidentifier not null (newid()) index AK_ShipMethod_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
PurchaseOrderHeader (Purchasing) ShipMethodID ShipMethodID
SalesOrderHeader (Sales) ShipMethodID ShipMethodID
Triggers
ShoppingCartItem (Sales)
Column Data Type Nullable Default PK / Index
ShoppingCartItemID int not null PK PK_ShoppingCartItem_ShoppingCartItemID
ShoppingCartID nvarchar(50) not null index IX_ShoppingCartItem_ShoppingCartID_ProductID
Quantity int not null ((1))
ProductID int not null index IX_ShoppingCartItem_ShoppingCartID_ProductID
DateCreated datetime not null (getdate())
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
Detail Table Column Referencing Column
Triggers
SpecialOffer (Sales)
Column Data Type Nullable Default PK / Index
SpecialOfferID int not null PK PK_SpecialOffer_SpecialOfferID
Description nvarchar(255) not null
DiscountPct smallmoney not null ((0.00))
Type nvarchar(50) not null
Category nvarchar(50) not null
StartDate datetime not null
EndDate datetime not null
MinQty int not null ((0))
MaxQty int null
rowguid uniqueidentifier not null (newid()) index AK_SpecialOffer_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
SpecialOfferProduct (Sales) SpecialOfferID SpecialOfferID
Triggers
SpecialOfferProduct (Sales)
Column Data Type Nullable Default PK / Index
SpecialOfferID int not null PK PK_SpecialOfferProduct_SpecialOfferID_ProductID
ProductID int not null PK PK_SpecialOfferProduct_SpecialOfferID_ProductID
index IX_SpecialOfferProduct_ProductID
rowguid uniqueidentifier not null (newid()) index AK_SpecialOfferProduct_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
SpecialOffer (Sales) SpecialOfferID SpecialOfferID
Detail Table Column Referencing Column
SalesOrderDetail (Sales) SpecialOfferID SpecialOfferID
SalesOrderDetail (Sales) SpecialOfferID SpecialOfferID
Triggers
StateProvince (Person)
Column Data Type Nullable Default PK / Index
StateProvinceID int not null PK PK_StateProvince_StateProvinceID
StateProvinceCode nchar(3) not null index AK_StateProvince_StateProvinceCode_CountryRegionCode
CountryRegionCode nvarchar(3) not null index AK_StateProvince_StateProvinceCode_CountryRegionCode
IsOnlyStateProvinceFlag dbo.Flag not null ((1))
Name dbo.Name not null index AK_StateProvince_Name
TerritoryID int not null
rowguid uniqueidentifier not null (newid()) index AK_StateProvince_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
CountryRegion (Person) CountryRegionCode CountryRegionCode
SalesTerritory (Sales) TerritoryID TerritoryID
Detail Table Column Referencing Column
Address (Person) StateProvinceID StateProvinceID
SalesTaxRate (Sales) StateProvinceID StateProvinceID
Triggers
Store (Sales)
Column Data Type Nullable Default PK / Index
CustomerID int not null PK PK_Store_CustomerID
Name dbo.Name not null
SalesPersonID int null index IX_Store_SalesPersonID
Demographics xml null index PXML_Store_Demographics
rowguid uniqueidentifier not null (newid()) index AK_Store_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Customer (Sales) CustomerID CustomerID
SalesPerson (Sales) SalesPersonID SalesPersonID
Detail Table Column Referencing Column
StoreContact (Sales) CustomerID CustomerID
Triggers
iStore ON INSERT
StoreContact (Sales)
Column Data Type Nullable Default PK / Index
CustomerID int not null PK PK_StoreContact_CustomerID_ContactID
ContactID int not null PK PK_StoreContact_CustomerID_ContactID
index IX_StoreContact_ContactID
ContactTypeID int not null index IX_StoreContact_ContactTypeID
rowguid uniqueidentifier not null (newid()) index AK_StoreContact_rowguid
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
ContactType (Person) ContactTypeID ContactTypeID
Store (Sales) CustomerID CustomerID
Detail Table Column Referencing Column
Triggers
TransactionHistory (Production)
Column Data Type Nullable Default PK / Index
TransactionID int not null PK PK_TransactionHistory_TransactionID
ProductID int not null index IX_TransactionHistory_ProductID
ReferenceOrderID int not null index IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
ReferenceOrderLineID int not null ((0)) index IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
TransactionDate datetime not null (getdate())
TransactionType nchar(1) not null
Quantity int not null
ActualCost money not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
Detail Table Column Referencing Column
Triggers
TransactionHistoryArchive (Production)
Column Data Type Nullable Default PK / Index
TransactionID int not null PK PK_TransactionHistoryArchive_TransactionID
ProductID int not null index IX_TransactionHistoryArchive_ProductID
ReferenceOrderID int not null index IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
ReferenceOrderLineID int not null ((0)) index IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
TransactionDate datetime not null (getdate())
TransactionType nchar(1) not null
Quantity int not null
ActualCost money not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
Triggers
UnitMeasure (Production)
Column Data Type Nullable Default PK / Index
UnitMeasureCode nchar(3) not null PK PK_UnitMeasure_UnitMeasureCode
Name dbo.Name not null index AK_UnitMeasure_Name
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
BillOfMaterials (Production) UnitMeasureCode UnitMeasureCode
Product (Production) UnitMeasureCode SizeUnitMeasureCode
Product (Production) UnitMeasureCode WeightUnitMeasureCode
ProductVendor (Purchasing) UnitMeasureCode UnitMeasureCode
Triggers
Vendor (Purchasing)
Column Data Type Nullable Default PK / Index
VendorID int not null PK PK_Vendor_VendorID
AccountNumber dbo.AccountNumber not null index AK_Vendor_AccountNumber
Name dbo.Name not null
CreditRating tinyint not null
PreferredVendorStatus dbo.Flag not null ((1))
ActiveFlag dbo.Flag not null ((1))
PurchasingWebServiceURL nvarchar(1024) null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Detail Table Column Referencing Column
ProductVendor (Purchasing) VendorID VendorID
PurchaseOrderHeader (Purchasing) VendorID VendorID
VendorAddress (Purchasing) VendorID VendorID
VendorContact (Purchasing) VendorID VendorID
Triggers
dVendor INSTEAD OF DELETE
VendorAddress (Purchasing)
Column Data Type Nullable Default PK / Index
VendorID int not null PK PK_VendorAddress_VendorID_AddressID
AddressID int not null PK PK_VendorAddress_VendorID_AddressID
index IX_VendorAddress_AddressID
AddressTypeID int not null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Address (Person) AddressID AddressID
AddressType (Person) AddressTypeID AddressTypeID
Vendor (Purchasing) VendorID VendorID
Detail Table Column Referencing Column
Triggers
VendorContact (Purchasing)
Column Data Type Nullable Default PK / Index
VendorID int not null PK PK_VendorContact_VendorID_ContactID
ContactID int not null PK PK_VendorContact_VendorID_ContactID
index IX_VendorContact_ContactID
ContactTypeID int not null index IX_VendorContact_ContactTypeID
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Contact (Person) ContactID ContactID
ContactType (Person) ContactTypeID ContactTypeID
Vendor (Purchasing) VendorID VendorID
Detail Table Column Referencing Column
Triggers
WorkOrder (Production)
Column Data Type Nullable Default PK / Index
WorkOrderID int not null PK PK_WorkOrder_WorkOrderID
ProductID int not null index IX_WorkOrder_ProductID
OrderQty int not null
StockedQty
ScrappedQty smallint not null
StartDate datetime not null
EndDate datetime null
DueDate datetime not null
ScrapReasonID smallint null index IX_WorkOrder_ScrapReasonID
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Product (Production) ProductID ProductID
ScrapReason (Production) ScrapReasonID ScrapReasonID
Detail Table Column Referencing Column
WorkOrderRouting (Production) WorkOrderID WorkOrderID
Triggers
iWorkOrder ON INSERT
uWorkOrder ON UPDATE
WorkOrderRouting (Production)
Column Data Type Nullable Default PK / Index
WorkOrderID int not null PK PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
ProductID int not null PK PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
index IX_WorkOrderRouting_ProductID
OperationSequence smallint not null PK PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence
LocationID smallint not null
ScheduledStartDate datetime not null
ScheduledEndDate datetime not null
ActualStartDate datetime null
ActualEndDate datetime null
ActualResourceHrs decimal(9, 4) null
PlannedCost money not null
ActualCost money null
ModifiedDate datetime not null (getdate())
Foreign Key Column Referenced Column
Location (Production) LocationID LocationID
WorkOrder (Production) WorkOrderID WorkOrderID
Detail Table Column Referencing Column
Triggers
Views
vAdditionalContactInfo (Person)
CREATE VIEW [Person].[vAdditionalContactInfo] 
AS 
SELECT 
    [ContactID] 
    ,[FirstName]
    ,[MiddleName]
    ,[LastName]
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
        (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress] 
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions] 
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
        (act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Person].[Contact]
OUTER APPLY [AdditionalContactInfo].nodes(
    'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"; 
    /ci:AdditionalContactInfo') AS ContactInfo(ref) 
WHERE [AdditionalContactInfo] IS NOT NULL;
vEmployee (HumanResources)
CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    e.[EmployeeID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,e.[Title] AS [JobTitle] 
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea 
    ON e.[EmployeeID] = ea.[EmployeeID] 
    INNER JOIN [Person].[Address] a 
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
vEmployeeDepartment (HumanResources)
CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,e.[Title] AS [JobTitle] 
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());
vEmployeeDepartmentHistory (HumanResources)
CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,s.[Name] AS [Shift]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
    ,edh.[EndDate]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
    INNER JOIN [HumanResources].[Shift] s
    ON s.[ShiftID] = edh.[ShiftID];
vIndividualCustomer (Sales)
CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    i.[CustomerID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,i.[Demographics]
FROM [Sales].[Individual] i
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = i.[ContactID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = i.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');
vIndividualDemographics (Sales)
CREATE VIEW [Sales].[vIndividualDemographics] 
AS 
SELECT 
    i.[CustomerID] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase] 
    ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Gender[1]', 'nvarchar(1)') AS [Gender] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        TotalChildren[1]', 'integer') AS [TotalChildren] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Education[1]', 'nvarchar(30)') AS [Education] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        Occupation[1]', 'nvarchar(30)') AS [Occupation] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag] 
    ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
        NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned] 
FROM [Sales].[Individual] i 
CROSS APPLY i.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
    /IndividualSurvey') AS [IndividualSurvey](ref) 
WHERE [Demographics] IS NOT NULL;
vJobCandidate (HumanResources)
CREATE VIEW [HumanResources].[vJobCandidate] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,jc.[EmployeeID] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
        (/Resume/Name/Name.First)[1]', 'nvarchar(30)') AS [Name.First] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Middle)[1]', 'nvarchar(30)') AS [Name.Middle] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Last)[1]', 'nvarchar(30)') AS [Name.Last] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Name/Name.Suffix)[1]', 'nvarchar(30)') AS [Name.Suffix] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/Skills)[1]', 'nvarchar(max)') AS [Skills] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Type)[1]', 'nvarchar(30)') AS [Addr.Type]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Addr.Loc.CountryRegion]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Addr.Loc.State]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Addr.Loc.City]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Address/Addr.PostalCode)[1]', 'nvarchar(20)') AS [Addr.PostalCode]
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/EMail)[1]', 'nvarchar(max)') AS [EMail] 
    ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (/Resume/WebSite)[1]', 'nvarchar(max)') AS [WebSite] 
    ,jc.[ModifiedDate] 
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume') AS Resume(ref);
vJobCandidateEducation (HumanResources)
CREATE VIEW [HumanResources].[vJobCandidateEducation] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Level)[1]', 'nvarchar(max)') AS [Edu.Level]
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.StartDate] 
    ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.EndDate] 
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Degree)[1]', 'nvarchar(50)') AS [Edu.Degree]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Major)[1]', 'nvarchar(50)') AS [Edu.Major]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Minor)[1]', 'nvarchar(50)') AS [Edu.Minor]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPA)[1]', 'nvarchar(5)') AS [Edu.GPA]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.GPAScale)[1]', 'nvarchar(5)') AS [Edu.GPAScale]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.School)[1]', 'nvarchar(100)') AS [Edu.School]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Edu.Loc.CountryRegion]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Edu.Loc.State]
    ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Edu.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Edu.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Education') AS [Education](ref);
vJobCandidateEmployment (HumanResources)
CREATE VIEW [HumanResources].[vJobCandidateEmployment] 
AS 
SELECT 
    jc.[JobCandidateID] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate] 
    ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate] 
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State]
    ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
        (Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.Loc.City]
FROM [HumanResources].[JobCandidate] jc 
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
    /Resume/Employment') AS Employment(ref);
vProductAndDescription (Production)
CREATE VIEW [Production].[vProductAndDescription] 
WITH SCHEMABINDING 
AS 
-- View (indexed or standard) to display products and product descriptions by language.
SELECT 
    p.[ProductID] 
    ,p.[Name] 
    ,pm.[Name] AS [ProductModel] 
    ,pmx.[CultureID] 
    ,pd.[Description] 
FROM [Production].[Product] p 
    INNER JOIN [Production].[ProductModel] pm 
    ON p.[ProductModelID] = pm.[ProductModelID] 
    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 
    ON pm.[ProductModelID] = pmx.[ProductModelID] 
    INNER JOIN [Production].[ProductDescription] pd 
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
vProductModelCatalogDescription (Production)
CREATE VIEW [Production].[vProductModelCatalogDescription] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace html="http://www.w3.org/1999/xhtml"; 
        (/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"; 
        (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures"; 
        (/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style] 
    ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; 
        (/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
WHERE [CatalogDescription] IS NOT NULL;
vProductModelInstructions (Production)
CREATE VIEW [Production].[vProductModelInstructions] 
AS 
SELECT 
    [ProductModelID] 
    ,[Name] 
    ,[Instructions].value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
        (/root/text())[1]', 'nvarchar(max)') AS [Instructions] 
    ,[MfgInstructions].ref.value('@LocationID[1]', 'int') AS [LocationID] 
    ,[MfgInstructions].ref.value('@SetupHours[1]', 'decimal(9, 4)') AS [SetupHours] 
    ,[MfgInstructions].ref.value('@MachineHours[1]', 'decimal(9, 4)') AS [MachineHours] 
    ,[MfgInstructions].ref.value('@LaborHours[1]', 'decimal(9, 4)') AS [LaborHours] 
    ,[MfgInstructions].ref.value('@LotSize[1]', 'int') AS [LotSize] 
    ,[Steps].ref.value('string(.)[1]', 'nvarchar(1024)') AS [Step] 
    ,[rowguid] 
    ,[ModifiedDate]
FROM [Production].[ProductModel] 
CROSS APPLY [Instructions].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    /root/Location') MfgInstructions(ref)
CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; 
    step') Steps(ref);
vSalesPerson (Sales)
CREATE VIEW [Sales].[vSalesPerson] 
AS 
SELECT 
    s.[SalesPersonID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,[JobTitle] = e.[Title]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
    ,s.[SalesQuota]
    ,s.[SalesYTD]
    ,s.[SalesLastYear]
FROM [Sales].[SalesPerson] s
    INNER JOIN [HumanResources].[Employee] e 
    ON e.[EmployeeID] = s.[SalesPersonID]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st 
    ON st.[TerritoryID] = s.[TerritoryID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea 
    ON e.[EmployeeID] = ea.[EmployeeID] 
    INNER JOIN [Person].[Address] a 
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
vSalesPersonSalesByFiscalYears (Sales)
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 
SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[Title]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
        ,e.[Title]
        ,st.[Name] AS [SalesTerritory]
        ,soh.[SubTotal]
        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
    FROM [Sales].[SalesPerson] sp 
        INNER JOIN [Sales].[SalesOrderHeader] soh 
        ON sp.[SalesPersonID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[EmployeeID] 
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.ContactID 
    ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;
vStateProvinceCountryRegion (Person)
CREATE VIEW [Person].[vStateProvinceCountryRegion] 
WITH SCHEMABINDING 
AS 
SELECT 
    sp.[StateProvinceID] 
    ,sp.[StateProvinceCode] 
    ,sp.[IsOnlyStateProvinceFlag] 
    ,sp.[Name] AS [StateProvinceName] 
    ,sp.[TerritoryID] 
    ,cr.[CountryRegionCode] 
    ,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp 
    INNER JOIN [Person].[CountryRegion] cr 
    ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
vStoreWithDemographics (Sales)
CREATE VIEW [Sales].[vStoreWithDemographics] AS 
SELECT 
    s.[CustomerID] 
    ,s.[Name] 
    ,ct.[Name] AS [ContactType] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,c.[Phone] 
    ,c.[EmailAddress] 
    ,c.[EmailPromotion] 
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1] 
    ,a.[AddressLine2] 
    ,a.[City] 
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode] 
    ,cr.[Name] AS [CountryRegionName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet] 
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees] 
FROM [Sales].[Store] s
    INNER JOIN [Sales].[StoreContact] sc 
    ON sc.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = sc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON sc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Sales].[CustomerAddress] ca 
    ON ca.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = ca.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] 
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
vVendor (Purchasing)
CREATE VIEW [Purchasing].[vVendor] AS 
SELECT 
    v.[VendorID]
    ,v.[Name]
    ,ct.[Name] AS [ContactType]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
FROM [Purchasing].[Vendor] v
    INNER JOIN [Purchasing].[VendorContact] vc 
    ON vc.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = vc.[ContactID]
    INNER JOIN [Person].[ContactType] ct 
    ON vc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Purchasing].[VendorAddress] va 
    ON va.[VendorID] = v.[VendorID]
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = va.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
Procedures
uspGetBillOfMaterials (dbo)
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this date.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE b.[ProductAssemblyID] = @StartProductID 
            AND @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b 
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25) 
END;
uspGetEmployeeManagers (dbo)
CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
    @EmployeeID [int]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e 
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        WHERE e.[EmployeeID] = @EmployeeID
        UNION ALL
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[EmployeeID] = [EMP_cte].[ManagerID]
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
    )
    -- Join back to Employee to return the manager name 
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
        [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
    FROM [EMP_cte] 
        INNER JOIN [HumanResources].[Employee] e 
        ON [EMP_cte].[ManagerID] = e.[EmployeeID]
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.[ContactID]
    ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
    OPTION (MAXRECURSION 25) 
END;
uspGetManagerEmployees (dbo)
CREATE PROCEDURE [dbo].[uspGetManagerEmployees]
    @ManagerID [int]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 -- Get the initial list of Employees for Manager n
        FROM [HumanResources].[Employee] e 
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        WHERE [ManagerID] = @ManagerID
        UNION ALL
        SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[ManagerID] = [EMP_cte].[EmployeeID]
            INNER JOIN [Person].[Contact] c 
            ON e.[ContactID] = c.[ContactID]
        )
    -- Join back to Employee to return the manager name 
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName',
        [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
    FROM [EMP_cte] 
        INNER JOIN [HumanResources].[Employee] e 
        ON [EMP_cte].[ManagerID] = e.[EmployeeID]
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.[ContactID]
    ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
    OPTION (MAXRECURSION 25) 
END;
uspGetWhereUsedProductID (dbo)
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    --Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p 
            ON b.[ProductAssemblyID] = p.[ProductID] 
        WHERE b.[ComponentID] = @StartProductID 
            AND @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b 
            ON cte.[ProductAssemblyID] = b.[ComponentID]
            INNER JOIN [Production].[Product] p 
            ON b.[ProductAssemblyID] = p.[ProductID] 
        WHERE @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25) 
END;
uspLogError (dbo)
-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;
uspPrintError (dbo)
-- uspPrintError prints error information about the error that caused 
-- execution to jump to the CATCH block of a TRY...CATCH construct. 
-- Should be executed from within the scope of a CATCH block otherwise 
-- it will return without printing any error information.
CREATE PROCEDURE [dbo].[uspPrintError] 
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information. 
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;
uspUpdateEmployeeHireInfo (HumanResources)
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID [int], 
    @Title [nvarchar](50), 
    @HireDate [datetime], 
    @RateChangeDate [datetime], 
    @Rate [money], 
    @PayFrequency [tinyint], 
    @CurrentFlag [dbo].[Flag] 
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE [HumanResources].[Employee] 
        SET [Title] = @Title 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [EmployeeID] = @EmployeeID;

        INSERT INTO [HumanResources].[EmployeePayHistory] 
            ([EmployeeID]
            ,[RateChangeDate]
            ,[Rate]
            ,[PayFrequency]) 
        VALUES (@EmployeeID, @RateChangeDate, @Rate, @PayFrequency);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
uspUpdateEmployeeLogin (HumanResources)
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @EmployeeID [int], 
    @ManagerID [int],
    @LoginID [nvarchar](256),
    @Title [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [ManagerID] = @ManagerID 
            ,[LoginID] = @LoginID 
            ,[Title] = @Title 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [EmployeeID] = @EmployeeID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
uspUpdateEmployeePersonalInfo (HumanResources)
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
    @EmployeeID [int], 
    @NationalIDNumber [nvarchar](15), 
    @BirthDate [datetime], 
    @MaritalStatus [nchar](1), 
    @Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [NationalIDNumber] = @NationalIDNumber 
            ,[BirthDate] = @BirthDate 
            ,[MaritalStatus] = @MaritalStatus 
            ,[Gender] = @Gender 
        WHERE [EmployeeID] = @EmployeeID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
Functions
ufnGetAccountingEndDate (dbo)
CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;
ufnGetAccountingStartDate (dbo)
CREATE FUNCTION [dbo].[ufnGetAccountingStartDate]()
RETURNS [datetime] 
AS 
BEGIN
    RETURN CONVERT(datetime, '20030701', 112);
END;
ufnGetDocumentStatusText (dbo)
CREATE FUNCTION [dbo].[ufnGetDocumentStatusText](@Status [tinyint])
RETURNS [nvarchar](16) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](16);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN N'Pending approval'
            WHEN 2 THEN N'Approved'
            WHEN 3 THEN N'Obsolete'
            ELSE N'** Invalid **'
        END;
    
    RETURN @ret
END;
ufnGetProductDealerPrice (dbo)
CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
-- Returns the dealer price for the product on a specific date.
BEGIN
    DECLARE @DealerPrice money;
    DECLARE @DealerDiscount money;

    SET @DealerDiscount = 0.60  -- 60% of list price

    SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @DealerPrice;
END;
ufnGetProductListPrice (dbo)
CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;

    SELECT @ListPrice = plph.[ListPrice] 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @ListPrice;
END;
ufnGetProductStandardCost (dbo)
CREATE FUNCTION [dbo].[ufnGetProductStandardCost](@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
-- Returns the standard cost for the product on a specific date.
BEGIN
    DECLARE @StandardCost money;

    SELECT @StandardCost = pch.[StandardCost] 
    FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductCostHistory] pch 
        ON p.[ProductID] = pch.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND @OrderDate BETWEEN pch.[StartDate] AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!

    RETURN @StandardCost;
END;
ufnGetPurchaseOrderStatusText (dbo)
CREATE FUNCTION [dbo].[ufnGetPurchaseOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'Pending'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Rejected'
            WHEN 4 THEN 'Complete'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;
ufnGetSalesOrderStatusText (dbo)
CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;
ufnGetStock (dbo)
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int] 
AS 
-- Returns the stock level for the product. This function is used internally only
BEGIN
    DECLARE @ret int;
    
    SELECT @ret = SUM(p.[Quantity]) 
    FROM [Production].[ProductInventory] p 
    WHERE p.[ProductID] = @ProductID 
        AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage
    
    IF (@ret IS NULL) 
        SET @ret = 0
    
    RETURN @ret
END;
ufnLeadingZeros (dbo)
CREATE FUNCTION [dbo].[ufnLeadingZeros](
    @Value int
) 
RETURNS varchar(8) 
WITH SCHEMABINDING 
AS 
BEGIN
    DECLARE @ReturnValue varchar(8);

    SET @ReturnValue = CONVERT(varchar(8), @Value);
    SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;

    RETURN (@ReturnValue);
END;
ufnGetContactInformation (dbo)
CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    [ContactID] int PRIMARY KEY NOT NULL, 
    [FirstName] [nvarchar](50) NULL, 
    [LastName] [nvarchar](50) NULL, 
    [JobTitle] [nvarchar](50) NULL, 
    [ContactType] [nvarchar](50) NULL
)
AS 
-- Returns the first name, last name, job title and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName [nvarchar](50), 
        @LastName [nvarchar](50), 
        @JobTitle [nvarchar](50), 
        @ContactType [nvarchar](50);

    -- Get common contact information
    SELECT 
        @ContactID = ContactID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM [Person].[Contact] 
    WHERE [ContactID] = @ContactID;

    SET @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e 
                WHERE e.[ContactID] = @ContactID) 
                THEN (SELECT [Title] 
                    FROM [HumanResources].[Employee] 
                    WHERE [ContactID] = @ContactID)

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON vc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE vc.[ContactID] = @ContactID) 
                THEN (SELECT ct.[Name] 
                    FROM [Purchasing].[VendorContact] vc 
                        INNER JOIN [Person].[ContactType] ct 
                        ON vc.[ContactTypeID] = ct.[ContactTypeID] 
                    WHERE vc.[ContactID] = @ContactID)

            -- Check for store
            WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON sc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE sc.[ContactID] = @ContactID) 
                THEN (SELECT ct.[Name] 
                    FROM [Sales].[StoreContact] sc 
                        INNER JOIN [Person].[ContactType] ct 
                        ON sc.[ContactTypeID] = ct.[ContactTypeID] 
                    WHERE [ContactID] = @ContactID)

            ELSE NULL 
        END;

    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e 
                WHERE e.[ContactID] = @ContactID) 
                THEN 'Employee'

            -- Check for vendor
            WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON vc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE vc.[ContactID] = @ContactID) 
                THEN 'Vendor Contact'

            -- Check for store
            WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc 
                    INNER JOIN [Person].[ContactType] ct 
                    ON sc.[ContactTypeID] = ct.[ContactTypeID] 
                WHERE sc.[ContactID] = @ContactID) 
                THEN 'Store Contact'

            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM [Sales].[Individual] i 
                WHERE i.[ContactID] = @ContactID) 
                THEN 'Consumer'
        END;

    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;

    RETURN;
END;
Synonyms
Schemas
HumanResources
CREATE SCHEMA HumanResources AUTHORIZATION dbo
Person
CREATE SCHEMA Person AUTHORIZATION dbo
Production
CREATE SCHEMA Production AUTHORIZATION dbo
Purchasing
CREATE SCHEMA Purchasing AUTHORIZATION dbo
Sales
CREATE SCHEMA Sales AUTHORIZATION dbo
Types
AccountNumber (dbo)
CREATE TYPE dbo.AccountNumber FROM nvarchar(30) NULL
Flag (dbo)
CREATE TYPE dbo.Flag FROM bit NOT NULL
Name (dbo)
CREATE TYPE dbo.[Name] FROM nvarchar(100) NULL
NameStyle (dbo)
CREATE TYPE dbo.NameStyle FROM bit NOT NULL
OrderNumber (dbo)
CREATE TYPE dbo.OrderNumber FROM nvarchar(50) NULL
Phone (dbo)
CREATE TYPE dbo.Phone FROM nvarchar(50) NULL
XML Schema Collections
AdditionalContactInfoSchemaCollection (Person)
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo"><xsd:element name="AdditionalContactInfo"><xsd:complexType mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord"><xsd:element name="ContactRecord"><xsd:complexType mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:choice minOccurs="0" maxOccurs="unbounded"><xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" /></xsd:choice><xsd:attribute name="date" type="xsd:date" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" elementFormDefault="qualified"><xsd:element name="eMail" type="t:eMailType" /><xsd:element name="facsimileTelephoneNumber" type="t:phoneNumberType" /><xsd:element name="homePostalAddress" type="t:addressType" /><xsd:element name="internationaliSDNNumber" type="t:phoneNumberType" /><xsd:element name="mobile" type="t:phoneNumberType" /><xsd:element name="pager" type="t:phoneNumberType" /><xsd:element name="physicalDeliveryOfficeName" type="t:addressType" /><xsd:element name="registeredAddress" type="t:addressType" /><xsd:element name="telephoneNumber" type="t:phoneNumberType" /><xsd:element name="telexNumber" type="t:phoneNumberType" /><xsd:complexType name="addressType"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Street" type="xsd:string" maxOccurs="2" /><xsd:element name="City" type="xsd:string" /><xsd:element name="StateProvince" type="xsd:string" /><xsd:element name="PostalCode" type="xsd:string" minOccurs="0" /><xsd:element name="CountryRegion" type="xsd:string" /><xsd:element name="SpecialInstructions" type="t:specialInstructionsType" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="eMailType"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="eMailAddress" type="xsd:string" /><xsd:element name="SpecialInstructions" type="t:specialInstructionsType" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="phoneNumberType"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="number"><xsd:simpleType><xsd:restriction base="xsd:string"><xsd:pattern value="[0-9\(\)\-]*" /></xsd:restriction></xsd:simpleType></xsd:element><xsd:element name="SpecialInstructions" type="t:specialInstructionsType" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="specialInstructionsType" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:schema>
HRResumeSchemaCollection (HumanResources)
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume" elementFormDefault="qualified">
  <xsd:element name="Address" type="t:AddressType" />
  <xsd:element name="Education" type="t:EducationType" />
  <xsd:element name="Employment" type="t:EmploymentType" />
  <xsd:element name="Location" type="t:LocationType" />
  <xsd:element name="Name" type="t:NameType" />
  <xsd:element name="Resume" type="t:ResumeType" />
  <xsd:element name="Telephone" type="t:TelephoneType" />
  <xsd:complexType name="AddressType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Addr.Type" type="xsd:string" />
          <xsd:element name="Addr.OrgName" type="xsd:string" minOccurs="0" />
          <xsd:element name="Addr.Street" type="xsd:string" maxOccurs="unbounded" />
          <xsd:element name="Addr.Location">
            <xsd:complexType>
              <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Location" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
          <xsd:element name="Addr.PostalCode" type="xsd:string" />
          <xsd:element name="Addr.Telephone" minOccurs="0">
            <xsd:complexType>
              <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Telephone" maxOccurs="unbounded" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="EducationType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Edu.Level" type="xsd:string" />
          <xsd:element name="Edu.StartDate" type="xsd:date" />
          <xsd:element name="Edu.EndDate" type="xsd:date" />
          <xsd:element name="Edu.Degree" type="xsd:string" minOccurs="0" />
          <xsd:element name="Edu.Major" type="xsd:string" minOccurs="0" />
          <xsd:element name="Edu.Minor" type="xsd:string" minOccurs="0" />
          <xsd:element name="Edu.GPA" type="xsd:string" minOccurs="0" />
          <xsd:element name="Edu.GPAAlternate" type="xsd:decimal" minOccurs="0" />
          <xsd:element name="Edu.GPAScale" type="xsd:decimal" minOccurs="0" />
          <xsd:element name="Edu.School" type="xsd:string" minOccurs="0" />
          <xsd:element name="Edu.Location" minOccurs="0">
            <xsd:complexType>
              <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Location" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="EmploymentType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Emp.StartDate" type="xsd:date" minOccurs="0" />
          <xsd:element name="Emp.EndDate" type="xsd:date" minOccurs="0" />
          <xsd:element name="Emp.OrgName" type="xsd:string" />
          <xsd:element name="Emp.JobTitle" type="xsd:string" />
          <xsd:element name="Emp.Responsibility" type="xsd:string" />
          <xsd:element name="Emp.FunctionCategory" type="xsd:string" minOccurs="0" />
          <xsd:element name="Emp.IndustryCategory" type="xsd:string" minOccurs="0" />
          <xsd:element name="Emp.Location" minOccurs="0">
            <xsd:complexType>
              <xsd:complexContent>
                <xsd:restriction base="xsd:anyType">
                  <xsd:sequence>
                    <xsd:element ref="t:Location" />
                  </xsd:sequence>
                </xsd:restriction>
              </xsd:complexContent>
            </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="LocationType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Loc.CountryRegion" type="xsd:string" />
          <xsd:element name="Loc.State" type="xsd:string" minOccurs="0" />
          <xsd:element name="Loc.City" type="xsd:string" minOccurs="0" />
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="NameType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Name.Prefix" type="xsd:string" minOccurs="0" />
          <xsd:element name="Name.First" type="xsd:string" />
          <xsd:element name="Name.Middle" type="xsd:string" minOccurs="0" />
          <xsd:element name="Name.Last" type="xsd:string" />
          <xsd:element name="Name.Suffix" type="xsd:string" minOccurs="0" />
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="ResumeType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element ref="t:Name" />
          <xsd:element name="Skills" type="xsd:string" minOccurs="0" />
          <xsd:element ref="t:Employment" maxOccurs="unbounded" />
          <xsd:element ref="t:Education" maxOccurs="unbounded" />
          <xsd:element ref="t:Address" maxOccurs="unbounded" />
          <xsd:element ref="t:Telephone" minOccurs="0" />
          <xsd:element name="EMail" type="xsd:string" minOccurs="0" />
          <xsd:element name="WebSite" type="xsd:string" minOccurs="0" />
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="TelephoneType">
    <xsd:complexContent>
      <xsd:restriction base="xsd:anyType">
        <xsd:sequence>
          <xsd:element name="Tel.Type" type="xsd:anyType" minOccurs="0" />
          <xsd:element name="Tel.IntlCode" type="xsd:int" minOccurs="0" />
          <xsd:element name="Tel.AreaCode" type="xsd:int" minOccurs="0" />
          <xsd:element name="Tel.Number" type="xsd:string" />
          <xsd:element name="Tel.Extension" type="xsd:int" minOccurs="0" />
        </xsd:sequence>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
</xsd:schema>
IndividualSurveySchemaCollection (Sales)
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" elementFormDefault="qualified">
  <xsd:element name="IndividualSurvey">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="TotalPurchaseYTD" type="xsd:decimal" minOccurs="0" />
            <xsd:element name="DateFirstPurchase" type="xsd:date" minOccurs="0" />
            <xsd:element name="BirthDate" type="xsd:date" minOccurs="0" />
            <xsd:element name="MaritalStatus" type="xsd:string" minOccurs="0" />
            <xsd:element name="YearlyIncome" type="t:SalaryType" minOccurs="0" />
            <xsd:element name="Gender" type="xsd:string" minOccurs="0" />
            <xsd:element name="TotalChildren" type="xsd:int" minOccurs="0" />
            <xsd:element name="NumberChildrenAtHome" type="xsd:int" minOccurs="0" />
            <xsd:element name="Education" type="xsd:string" minOccurs="0" />
            <xsd:element name="Occupation" type="xsd:string" minOccurs="0" />
            <xsd:element name="HomeOwnerFlag" type="xsd:string" minOccurs="0" />
            <xsd:element name="NumberCarsOwned" type="xsd:int" minOccurs="0" />
            <xsd:element name="Hobby" type="xsd:string" minOccurs="0" maxOccurs="unbounded" />
            <xsd:element name="CommuteDistance" type="t:MileRangeType" minOccurs="0" />
            <xsd:element name="Comments" type="xsd:string" minOccurs="0" />
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
  <xsd:simpleType name="MileRangeType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="0-1 Miles" />
      <xsd:enumeration value="1-2 Miles" />
      <xsd:enumeration value="2-5 Miles" />
      <xsd:enumeration value="5-10 Miles" />
      <xsd:enumeration value="10+ Miles" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:simpleType name="SalaryType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="0-25000" />
      <xsd:enumeration value="25001-50000" />
      <xsd:enumeration value="50001-75000" />
      <xsd:enumeration value="75001-100000" />
      <xsd:enumeration value="greater than 100000" />
    </xsd:restriction>
  </xsd:simpleType>
</xsd:schema>
ManuInstructionsSchemaCollection (Production)
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" elementFormDefault="qualified">
  <xsd:element name="root">
    <xsd:complexType mixed="true">
      <xsd:complexContent mixed="true">
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="Location" maxOccurs="unbounded">
              <xsd:complexType mixed="true">
                <xsd:complexContent mixed="true">
                  <xsd:restriction base="xsd:anyType">
                    <xsd:sequence>
                      <xsd:element name="step" type="t:StepType" maxOccurs="unbounded" />
                    </xsd:sequence>
                    <xsd:attribute name="LocationID" type="xsd:integer" use="required" />
                    <xsd:attribute name="SetupHours" type="xsd:decimal" />
                    <xsd:attribute name="MachineHours" type="xsd:decimal" />
                    <xsd:attribute name="LaborHours" type="xsd:decimal" />
                    <xsd:attribute name="LotSize" type="xsd:decimal" />
                  </xsd:restriction>
                </xsd:complexContent>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
  <xsd:complexType name="StepType" mixed="true">
    <xsd:complexContent mixed="true">
      <xsd:restriction base="xsd:anyType">
        <xsd:choice minOccurs="0" maxOccurs="unbounded">
          <xsd:element name="tool" type="xsd:string" />
          <xsd:element name="material" type="xsd:string" />
          <xsd:element name="blueprint" type="xsd:string" />
          <xsd:element name="specs" type="xsd:string" />
          <xsd:element name="diag" type="xsd:string" />
        </xsd:choice>
      </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
</xsd:schema>
ProductDescriptionSchemaCollection (Production)
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" elementFormDefault="qualified"><xsd:element name="Maintenance"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="NoOfYears" type="xsd:string" /><xsd:element name="Description" type="xsd:string" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:element name="Warranty"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="WarrantyPeriod" type="xsd:string" /><xsd:element name="Description" type="xsd:string" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" elementFormDefault="qualified"><xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" /><xsd:element name="Code" type="xsd:string" /><xsd:element name="Description" type="xsd:string" /><xsd:element name="ProductDescription" type="t:ProductDescription" /><xsd:element name="Taxonomy" type="xsd:string" /><xsd:complexType name="Category"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element ref="t:Taxonomy" /><xsd:element ref="t:Code" /><xsd:element ref="t:Description" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Features" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element ref="ns1:Warranty" /><xsd:element ref="ns1:Maintenance" /><xsd:any namespace="##other" processContents="skip" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Manufacturer"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Name" type="xsd:string" minOccurs="0" /><xsd:element name="CopyrightURL" type="xsd:string" minOccurs="0" /><xsd:element name="Copyright" type="xsd:string" minOccurs="0" /><xsd:element name="ProductURL" type="xsd:string" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Picture"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Name" type="xsd:string" minOccurs="0" /><xsd:element name="Angle" type="xsd:string" minOccurs="0" /><xsd:element name="Size" type="xsd:string" minOccurs="0" /><xsd:element name="ProductPhotoID" type="xsd:integer" minOccurs="0" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="ProductDescription"><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Summary" type="t:Summary" minOccurs="0" /><xsd:element name="Manufacturer" type="t:Manufacturer" minOccurs="0" /><xsd:element name="Features" type="t:Features" minOccurs="0" maxOccurs="unbounded" /><xsd:element name="Picture" type="t:Picture" minOccurs="0" maxOccurs="unbounded" /><xsd:element name="Category" type="t:Category" minOccurs="0" maxOccurs="unbounded" /><xsd:element name="Specifications" type="t:Specifications" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence><xsd:attribute name="ProductModelID" type="xsd:string" /><xsd:attribute name="ProductModelName" type="xsd:string" /></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Specifications" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any processContents="skip" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType><xsd:complexType name="Summary" mixed="true"><xsd:complexContent mixed="true"><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:any namespace="http://www.w3.org/1999/xhtml" processContents="skip" minOccurs="0" maxOccurs="unbounded" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:schema>
StoreSurveySchemaCollection (Sales)
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" elementFormDefault="qualified">
  <xsd:element name="StoreSurvey">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="ContactName" type="xsd:string" minOccurs="0" />
            <xsd:element name="JobTitle" type="xsd:string" minOccurs="0" />
            <xsd:element name="AnnualSales" type="xsd:decimal" minOccurs="0" />
            <xsd:element name="AnnualRevenue" type="xsd:decimal" minOccurs="0" />
            <xsd:element name="BankName" type="xsd:string" minOccurs="0" />
            <xsd:element name="BusinessType" type="t:BusinessType" minOccurs="0" />
            <xsd:element name="YearOpened" type="xsd:gYear" minOccurs="0" />
            <xsd:element name="Specialty" type="t:SpecialtyType" minOccurs="0" />
            <xsd:element name="SquareFeet" type="xsd:float" minOccurs="0" />
            <xsd:element name="Brands" type="t:BrandType" minOccurs="0" />
            <xsd:element name="Internet" type="t:InternetType" minOccurs="0" />
            <xsd:element name="NumberEmployees" type="xsd:int" minOccurs="0" />
            <xsd:element name="Comments" type="xsd:string" minOccurs="0" />
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
  <xsd:simpleType name="BrandType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="AW" />
      <xsd:enumeration value="2" />
      <xsd:enumeration value="3" />
      <xsd:enumeration value="4+" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:simpleType name="BusinessType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="BM" />
      <xsd:enumeration value="BS" />
      <xsd:enumeration value="D" />
      <xsd:enumeration value="OS" />
      <xsd:enumeration value="SGS" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:simpleType name="InternetType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="56kb" />
      <xsd:enumeration value="ISDN" />
      <xsd:enumeration value="DSL" />
      <xsd:enumeration value="T1" />
      <xsd:enumeration value="T2" />
      <xsd:enumeration value="T3" />
    </xsd:restriction>
  </xsd:simpleType>
  <xsd:simpleType name="SpecialtyType">
    <xsd:restriction base="xsd:string">
      <xsd:enumeration value="Family" />
      <xsd:enumeration value="Kids" />
      <xsd:enumeration value="BMX" />
      <xsd:enumeration value="Touring" />
      <xsd:enumeration value="Road" />
      <xsd:enumeration value="Mountain" />
      <xsd:enumeration value="All" />
    </xsd:restriction>
  </xsd:simpleType>
</xsd:schema>

generated by dbscript