Table of Contents
Table |
Person.
Address |
Description |
Street address information for customers, employees, and vendors. |
Column |
Data Type |
Nullable |
Default |
Description |
AddressID |
int |
not null |
|
Primary key for Address records. |
AddressLine1 |
nvarchar(60) |
not null |
|
First street address line. |
AddressLine2 |
nvarchar(60) |
null |
|
Second street address line. |
City |
nvarchar(30) |
not null |
|
Name of the city. |
StateProvinceID |
int |
not null |
|
Unique identification number for the state or province. Foreign key to StateProvince table. |
PostalCode |
nvarchar(15) |
not null |
|
Postal code for the street address. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Address_AddressID |
AddressID |
Index |
Type |
Columns |
AK_Address_rowguid |
Unique |
rowguid |
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode |
Unique |
AddressLine1, AddressLine2, City, StateProvinceID, PostalCode |
IX_Address_StateProvinceID |
|
StateProvinceID |
Relation |
Column |
Referenced Column |
Person.StateProvince |
StateProvinceID |
StateProvinceID |
Detail Table |
Column |
Referencing Column |
Sales.CustomerAddress |
AddressID |
AddressID |
HumanResources.EmployeeAddress |
AddressID |
AddressID |
Sales.SalesOrderHeader |
AddressID |
BillToAddressID |
Sales.SalesOrderHeader |
AddressID |
ShipToAddressID |
Purchasing.VendorAddress |
AddressID |
AddressID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Person |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployee |
Select |
View |
Purchasing.
vVendor |
Select |
View |
Sales.
vIndividualCustomer |
Select |
View |
Sales.
vSalesPerson |
Select |
View |
Sales.
vStoreWithDemographics |
Table |
Person.
AddressType |
Description |
Types of addresses stored in the Address table. |
Column |
Data Type |
Nullable |
Default |
Description |
AddressTypeID |
int |
not null |
|
Primary key for AddressType records. |
Name |
dbo.Name |
not null |
|
Address type description. For example, Billing, Home, or Shipping. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_AddressType_AddressTypeID |
AddressTypeID |
Index |
Type |
Columns |
AK_AddressType_rowguid |
Unique |
rowguid |
AK_AddressType_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
Sales.CustomerAddress |
AddressTypeID |
AddressTypeID |
Purchasing.VendorAddress |
AddressTypeID |
AddressTypeID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Person |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Sales.
vIndividualCustomer |
Select |
View |
Sales.
vStoreWithDemographics |
Table |
dbo.
AWBuildVersion |
Description |
Current version number of the AdventureWorks sample database. |
Column |
Data Type |
Nullable |
Default |
Description |
SystemInformationID |
tinyint |
not null |
|
Primary key for AWBuildVersion records. |
Database Version |
nvarchar(25) |
not null |
|
Version number of the database in 9.yy.mm.dd.00 format. |
VersionDate |
datetime |
not null |
|
Date and time the record was last updated. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_AWBuildVersion_SystemInformationID |
SystemInformationID |
Table |
Production.
BillOfMaterials |
Description |
Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components. |
Column |
Data Type |
Nullable |
Default |
Description |
BillOfMaterialsID |
int |
not null |
|
Primary key for BillOfMaterials records. |
ProductAssemblyID |
int |
null |
|
Parent product identification number. Foreign key to Product.ProductID. |
ComponentID |
int |
not null |
|
Component identification number. Foreign key to Product.ProductID. |
StartDate |
datetime |
not null |
(getdate())
|
Date the component started being used in the assembly item. |
EndDate |
datetime |
null |
|
Date the component stopped being used in the assembly item. |
UnitMeasureCode |
nchar(3) |
not null |
|
Standard code identifying the unit of measure for the quantity. |
BOMLevel |
smallint |
not null |
|
Indicates the depth the component is from its parent (AssemblyID). |
PerAssemblyQty |
decimal(8, 2) |
not null |
((1.00))
|
Quantity of the component needed to create the assembly. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_BillOfMaterials_BillOfMaterialsID |
BillOfMaterialsID |
Index |
Type |
Columns |
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate |
Unique |
ProductAssemblyID, ComponentID, StartDate |
IX_BillOfMaterials_UnitMeasureCode |
|
UnitMeasureCode |
Check Constraint |
Expression |
Description |
CK_BillOfMaterials_BOMLevel |
([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)) |
Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1) |
CK_BillOfMaterials_EndDate |
([EndDate]>[StartDate] OR [EndDate] IS NULL) |
Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL |
CK_BillOfMaterials_PerAssemblyQty |
([PerAssemblyQty]>=(1.00)) |
Check constraint [PerAssemblyQty] >= (1.00) |
CK_BillOfMaterials_ProductAssemblyID |
([ProductAssemblyID]<>[ComponentID]) |
Check constraint [ProductAssemblyID] <> [ComponentID] |
Relation |
Column |
Referenced Column |
Production.Product |
ComponentID |
ProductID |
Production.Product |
ProductAssemblyID |
ProductID |
Production.UnitMeasure |
UnitMeasureCode |
UnitMeasureCode |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Select |
Procedure |
dbo.
uspGetBillOfMaterials |
Select |
Procedure |
dbo.
uspGetWhereUsedProductID |
Table |
Person.
Contact |
Description |
Names of each employee, customer contact, and vendor contact. |
Column |
Data Type |
Nullable |
Default |
Description |
ContactID |
int |
not null |
|
Primary key for Contact records. |
NameStyle |
dbo.NameStyle |
not null |
((0))
|
0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order. |
Title |
nvarchar(8) |
null |
|
A courtesy title. For example, Mr. or Ms. |
FirstName |
dbo.Name |
not null |
|
First name of the person. |
MiddleName |
dbo.Name |
null |
|
Middle name or middle initial of the person. |
LastName |
dbo.Name |
not null |
|
Last name of the person. |
Suffix |
nvarchar(10) |
null |
|
Surname suffix. For example, Sr. or Jr. |
EmailAddress |
nvarchar(50) |
null |
|
E-mail address for the person. |
EmailPromotion |
int |
not null |
((0))
|
0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners. |
Phone |
dbo.Phone |
null |
|
Phone number associated with the person. |
PasswordHash |
varchar(128) |
not null |
|
Password for the e-mail account. |
PasswordSalt |
varchar(10) |
not null |
|
Random value concatenated with the password string before the password is hashed. |
AdditionalContactInfo |
xml |
null |
|
Additional contact information about the person stored in xml format. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Contact_ContactID |
ContactID |
Index |
Type |
Columns |
AK_Contact_rowguid |
Unique |
rowguid |
IX_Contact_EmailAddress |
|
EmailAddress |
PXML_Contact_AddContact |
|
AdditionalContactInfo |
Check Constraint |
Expression |
Description |
CK_Contact_EmailPromotion |
([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)) |
Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2) |
Detail Table |
Column |
Referencing Column |
Sales.ContactCreditCard |
ContactID |
ContactID |
HumanResources.Employee |
ContactID |
ContactID |
Sales.Individual |
ContactID |
ContactID |
Sales.SalesOrderHeader |
ContactID |
ContactID |
Sales.StoreContact |
ContactID |
ContactID |
Purchasing.VendorContact |
ContactID |
ContactID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Data Type |
Type |
dbo.
NameStyle |
Data Type |
Type |
dbo.
Phone |
Schema |
Schema |
Person |
Data Type |
XML Schema Collection |
Person.
AdditionalContactInfoSchemaCollection |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployee |
Select |
View |
HumanResources.
vEmployeeDepartment |
Select |
View |
HumanResources.
vEmployeeDepartmentHistory |
Select |
View |
Person.
vAdditionalContactInfo |
Select |
View |
Purchasing.
vVendor |
Select |
View |
Sales.
vIndividualCustomer |
Select |
View |
Sales.
vSalesPerson |
Select |
View |
Sales.
vSalesPersonSalesByFiscalYears |
Select |
View |
Sales.
vStoreWithDemographics |
Select |
Procedure |
dbo.
uspGetEmployeeManagers |
Select |
Procedure |
dbo.
uspGetManagerEmployees |
Select |
SQL table-valued-function |
dbo.
ufnGetContactInformation |
Table |
Sales.
ContactCreditCard |
Description |
Cross-reference table mapping customers in the Contact table to their credit card information in the CreditCard table. |
Column |
Data Type |
Nullable |
Default |
Description |
ContactID |
int |
not null |
|
Customer identification number. Foreign key to Contact.ContactID. |
CreditCardID |
int |
not null |
|
Credit card identification number. Foreign key to CreditCard.CreditCardID. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ContactCreditCard_ContactID_CreditCardID |
ContactID, CreditCardID |
Relation |
Column |
Referenced Column |
Person.Contact |
ContactID |
ContactID |
Sales.CreditCard |
CreditCardID |
CreditCardID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Person.
ContactType |
Description |
Lookup table containing the types of contacts stored in Contact. |
Column |
Data Type |
Nullable |
Default |
Description |
ContactTypeID |
int |
not null |
|
Primary key for ContactType records. |
Name |
dbo.Name |
not null |
|
Contact type description. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ContactType_ContactTypeID |
ContactTypeID |
Index |
Type |
Columns |
AK_ContactType_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
Sales.StoreContact |
ContactTypeID |
ContactTypeID |
Purchasing.VendorContact |
ContactTypeID |
ContactTypeID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Person |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Purchasing.
vVendor |
Select |
View |
Sales.
vStoreWithDemographics |
Select |
SQL table-valued-function |
dbo.
ufnGetContactInformation |
Table |
Person.
CountryRegion |
Description |
Lookup table containing the ISO standard codes for countries and regions. |
Column |
Data Type |
Nullable |
Default |
Description |
CountryRegionCode |
nvarchar(3) |
not null |
|
ISO standard code for countries and regions. |
Name |
dbo.Name |
not null |
|
Country or region name. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_CountryRegion_CountryRegionCode |
CountryRegionCode |
Index |
Type |
Columns |
AK_CountryRegion_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
Sales.CountryRegionCurrency |
CountryRegionCode |
CountryRegionCode |
Person.StateProvince |
CountryRegionCode |
CountryRegionCode |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Person |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployee |
Select |
View |
Person.
vStateProvinceCountryRegion |
Select |
View |
Purchasing.
vVendor |
Select |
View |
Sales.
vIndividualCustomer |
Select |
View |
Sales.
vSalesPerson |
Select |
View |
Sales.
vStoreWithDemographics |
Table |
Sales.
CountryRegionCurrency |
Description |
Cross-reference table mapping ISO currency codes to a country or region. |
Column |
Data Type |
Nullable |
Default |
Description |
CountryRegionCode |
nvarchar(3) |
not null |
|
ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode. |
CurrencyCode |
nchar(3) |
not null |
|
ISO standard currency code. Foreign key to Currency.CurrencyCode. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode |
CountryRegionCode, CurrencyCode |
Index |
Type |
Columns |
IX_CountryRegionCurrency_CurrencyCode |
|
CurrencyCode |
Relation |
Column |
Referenced Column |
Person.CountryRegion |
CountryRegionCode |
CountryRegionCode |
Sales.Currency |
CurrencyCode |
CurrencyCode |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Sales.
CreditCard |
Description |
Customer credit card information. |
Column |
Data Type |
Nullable |
Default |
Description |
CreditCardID |
int |
not null |
|
Primary key for CreditCard records. |
CardType |
nvarchar(50) |
not null |
|
Credit card name. |
CardNumber |
nvarchar(25) |
not null |
|
Credit card number. |
ExpMonth |
tinyint |
not null |
|
Credit card expiration month. |
ExpYear |
smallint |
not null |
|
Credit card expiration year. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_CreditCard_CreditCardID |
CreditCardID |
Index |
Type |
Columns |
AK_CreditCard_CardNumber |
Unique |
CardNumber |
Detail Table |
Column |
Referencing Column |
Sales.ContactCreditCard |
CreditCardID |
CreditCardID |
Sales.SalesOrderHeader |
CreditCardID |
CreditCardID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Production.
Culture |
Description |
Lookup table containing the languages in which some AdventureWorks data is stored. |
Column |
Data Type |
Nullable |
Default |
Description |
CultureID |
nchar(6) |
not null |
|
Primary key for Culture records. |
Name |
dbo.Name |
not null |
|
Culture description. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Culture_CultureID |
CultureID |
Index |
Type |
Columns |
AK_Culture_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
Production.ProductModelProductDescriptionCulture |
CultureID |
CultureID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Table |
Sales.
Currency |
Description |
Lookup table containing standard ISO currencies. |
Column |
Data Type |
Nullable |
Default |
Description |
CurrencyCode |
nchar(3) |
not null |
|
The ISO code for the Currency. |
Name |
dbo.Name |
not null |
|
Currency name. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Currency_CurrencyCode |
CurrencyCode |
Index |
Type |
Columns |
AK_Currency_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
Sales.CountryRegionCurrency |
CurrencyCode |
CurrencyCode |
Sales.CurrencyRate |
CurrencyCode |
FromCurrencyCode |
Sales.CurrencyRate |
CurrencyCode |
ToCurrencyCode |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Sales |
Table |
Sales.
CurrencyRate |
Description |
Currency exchange rates. |
Column |
Data Type |
Nullable |
Default |
Description |
CurrencyRateID |
int |
not null |
|
Primary key for CurrencyRate records. |
CurrencyRateDate |
datetime |
not null |
|
Date and time the exchange rate was obtained. |
FromCurrencyCode |
nchar(3) |
not null |
|
Exchange rate was converted from this currency code. |
ToCurrencyCode |
nchar(3) |
not null |
|
Exchange rate was converted to this currency code. |
AverageRate |
money |
not null |
|
Average exchange rate for the day. |
EndOfDayRate |
money |
not null |
|
Final exchange rate for the day. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_CurrencyRate_CurrencyRateID |
CurrencyRateID |
Index |
Type |
Columns |
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode |
Unique |
CurrencyRateDate, FromCurrencyCode, ToCurrencyCode |
Relation |
Column |
Referenced Column |
Sales.Currency |
FromCurrencyCode |
CurrencyCode |
Sales.Currency |
ToCurrencyCode |
CurrencyCode |
Detail Table |
Column |
Referencing Column |
Sales.SalesOrderHeader |
CurrencyRateID |
CurrencyRateID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Sales.
Customer |
Description |
Current customer information. Also see the Individual and Store tables. |
Column |
Data Type |
Nullable |
Default |
Description |
CustomerID |
int |
not null |
|
Primary key for Customer records. |
TerritoryID |
int |
null |
|
ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID. |
AccountNumber |
|
|
|
Unique number identifying the customer assigned by the accounting system. |
CustomerType |
nchar(1) |
not null |
|
Customer type: I = Individual, S = Store |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Customer_CustomerID |
CustomerID |
Index |
Type |
Columns |
AK_Customer_rowguid |
Unique |
rowguid |
AK_Customer_AccountNumber |
Unique |
AccountNumber |
IX_Customer_TerritoryID |
|
TerritoryID |
Check Constraint |
Expression |
Description |
CK_Customer_CustomerType |
(upper([CustomerType])='I' OR upper([CustomerType])='S') |
Check constraint [CustomerType]='I' OR [CustomerType]='i' OR [CustomerType]='S' OR [CustomerType]='s' |
Relation |
Column |
Referenced Column |
Sales.SalesTerritory |
TerritoryID |
TerritoryID |
Detail Table |
Column |
Referencing Column |
Sales.CustomerAddress |
CustomerID |
CustomerID |
Sales.Individual |
CustomerID |
CustomerID |
Sales.SalesOrderHeader |
CustomerID |
CustomerID |
Sales.Store |
CustomerID |
CustomerID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Sales.
vIndividualCustomer |
Select |
View |
Sales.
vStoreWithDemographics |
Table |
Sales.
CustomerAddress |
Description |
Cross-reference table mapping customers to their address(es). |
Column |
Data Type |
Nullable |
Default |
Description |
CustomerID |
int |
not null |
|
Primary key. Foreign key to Customer.CustomerID. |
AddressID |
int |
not null |
|
Primary key. Foreign key to Address.AddressID. |
AddressTypeID |
int |
not null |
|
Address type. Foreign key to AddressType.AddressTypeID. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_CustomerAddress_CustomerID_AddressID |
CustomerID, AddressID |
Index |
Type |
Columns |
AK_CustomerAddress_rowguid |
Unique |
rowguid |
Relation |
Column |
Referenced Column |
Person.Address |
AddressID |
AddressID |
Person.AddressType |
AddressTypeID |
AddressTypeID |
Sales.Customer |
CustomerID |
CustomerID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Sales.
vIndividualCustomer |
Select |
View |
Sales.
vStoreWithDemographics |
Table |
dbo.
DatabaseLog |
Description |
Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog. |
Column |
Data Type |
Nullable |
Default |
Description |
DatabaseLogID |
int |
not null |
|
Primary key for DatabaseLog records. |
PostTime |
datetime |
not null |
|
The date and time the DDL change occurred. |
DatabaseUser |
sysname |
not null |
|
The user who implemented the DDL change. |
Event |
sysname |
not null |
|
The type of DDL statement that was executed. |
Schema |
sysname |
null |
|
The schema to which the changed object belongs. |
Object |
sysname |
null |
|
The object that was changed by the DDL statment. |
TSQL |
nvarchar(max) |
not null |
|
The exact Transact-SQL statement that was executed. |
XmlEvent |
xml |
not null |
|
The raw XML data generated by database trigger. |
Primary Key |
Columns |
PK_DatabaseLog_DatabaseLogID |
DatabaseLogID |
Reference Type |
Object Type |
Referencing Object |
Insert |
Database Trigger |
ddlDatabaseTriggerLog |
Table |
HumanResources.
Department |
Description |
Lookup table containing the departments within the Adventure Works Cycles company. |
Column |
Data Type |
Nullable |
Default |
Description |
DepartmentID |
smallint |
not null |
|
Primary key for Department records. |
Name |
dbo.Name |
not null |
|
Name of the department. |
GroupName |
dbo.Name |
not null |
|
Name of the group to which the department belongs. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Department_DepartmentID |
DepartmentID |
Index |
Type |
Columns |
AK_Department_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
HumanResources.EmployeeDepartmentHistory |
DepartmentID |
DepartmentID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
HumanResources |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployeeDepartment |
Select |
View |
HumanResources.
vEmployeeDepartmentHistory |
Table |
Production.
Document |
Description |
Product maintenance documents. |
Column |
Data Type |
Nullable |
Default |
Description |
DocumentID |
int |
not null |
|
Primary key for Document records. |
Title |
nvarchar(50) |
not null |
|
Title of the document. |
FileName |
nvarchar(400) |
not null |
|
Directory path and file name of the document |
FileExtension |
nvarchar(8) |
not null |
|
File extension indicating the document type. For example, .doc or .txt. |
Revision |
nchar(5) |
not null |
|
Revision number of the document. |
ChangeNumber |
int |
not null |
((0))
|
Engineering change approval number. |
Status |
tinyint |
not null |
|
1 = Pending approval, 2 = Approved, 3 = Obsolete |
DocumentSummary |
nvarchar(max) |
null |
|
Document abstract. |
Document |
varbinary(max) |
null |
|
Complete document. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Document_DocumentID |
DocumentID |
Index |
Type |
Columns |
AK_Document_FileName_Revision |
Unique |
FileName, Revision |
Check Constraint |
Expression |
Description |
CK_Document_Status |
([Status]>=(1) AND [Status]<=(3)) |
Check constraint [Status] BETWEEN (1) AND (3) |
Detail Table |
Column |
Referencing Column |
Production.ProductDocument |
DocumentID |
DocumentID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Table |
HumanResources.
Employee |
Description |
Employee information such as salary, department, and title. |
Column |
Data Type |
Nullable |
Default |
Description |
EmployeeID |
int |
not null |
|
Primary key for Employee records. |
NationalIDNumber |
nvarchar(15) |
not null |
|
Unique national identification number such as a social security number. |
ContactID |
int |
not null |
|
Identifies the employee in the Contact table. Foreign key to Contact.ContactID. |
LoginID |
nvarchar(256) |
not null |
|
Network login. |
ManagerID |
int |
null |
|
Manager to whom the employee is assigned. Foreign Key to Employee.M |
Title |
nvarchar(50) |
not null |
|
Work title such as Buyer or Sales Representative. |
BirthDate |
datetime |
not null |
|
Date of birth. |
MaritalStatus |
nchar(1) |
not null |
|
M = Married, S = Single |
Gender |
nchar(1) |
not null |
|
M = Male, F = Female |
HireDate |
datetime |
not null |
|
Employee hired on this date. |
SalariedFlag |
dbo.Flag |
not null |
((1))
|
Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining. |
VacationHours |
smallint |
not null |
((0))
|
Number of available vacation hours. |
SickLeaveHours |
smallint |
not null |
((0))
|
Number of available sick leave hours. |
CurrentFlag |
dbo.Flag |
not null |
((1))
|
0 = Inactive, 1 = Active |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Employee_EmployeeID |
EmployeeID |
Index |
Type |
Columns |
AK_Employee_LoginID |
Unique |
LoginID |
AK_Employee_NationalIDNumber |
Unique |
NationalIDNumber |
AK_Employee_rowguid |
Unique |
rowguid |
IX_Employee_ManagerID |
|
ManagerID |
Check Constraint |
Expression |
Description |
CK_Employee_BirthDate |
([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())) |
Check constraint [BirthDate] >= '1930-01-01' AND [BirthDate] <= dateadd(year,(-18),GETDATE()) |
CK_Employee_Gender |
(upper([Gender])='F' OR upper([Gender])='M') |
Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M' |
CK_Employee_HireDate |
([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())) |
Check constraint [HireDate] >= '1996-07-01' AND [HireDate] <= dateadd(day,(1),GETDATE()) |
CK_Employee_MaritalStatus |
(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M') |
Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M' |
CK_Employee_SickLeaveHours |
([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)) |
Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120) |
CK_Employee_VacationHours |
([VacationHours]>=(-40) AND [VacationHours]<=(240)) |
Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240) |
Relation |
Column |
Referenced Column |
Person.Contact |
ContactID |
ContactID |
HumanResources.Employee |
ManagerID |
EmployeeID |
Detail Table |
Column |
Referencing Column |
HumanResources.Employee |
EmployeeID |
ManagerID |
HumanResources.EmployeeAddress |
EmployeeID |
EmployeeID |
HumanResources.EmployeeDepartmentHistory |
EmployeeID |
EmployeeID |
HumanResources.EmployeePayHistory |
EmployeeID |
EmployeeID |
HumanResources.JobCandidate |
EmployeeID |
EmployeeID |
Purchasing.PurchaseOrderHeader |
EmployeeID |
EmployeeID |
Sales.SalesPerson |
EmployeeID |
SalesPersonID |
Trigger |
Type |
dEmployee |
INSTEAD OF DELETE |
CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee] INSTEAD OF DELETE NOT FOR REPLICATION AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN RAISERROR (N'Employees cannot be deleted. They can only be marked as not current.', -- Message 10, -- Severity. 1); -- State. -- Rollback any active or uncommittable transactions IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END END; END;
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Flag |
Schema |
Schema |
HumanResources |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployee |
Select |
View |
HumanResources.
vEmployeeDepartment |
Select |
View |
HumanResources.
vEmployeeDepartmentHistory |
Select |
View |
Sales.
vSalesPerson |
Select |
View |
Sales.
vSalesPersonSalesByFiscalYears |
Select |
Procedure |
dbo.
uspGetEmployeeManagers |
Select |
Procedure |
dbo.
uspGetManagerEmployees |
Update |
Procedure |
HumanResources.
uspUpdateEmployeeHireInfo |
Update |
Procedure |
HumanResources.
uspUpdateEmployeeLogin |
Update |
Procedure |
HumanResources.
uspUpdateEmployeePersonalInfo |
Select |
SQL table-valued-function |
dbo.
ufnGetContactInformation |
Table |
HumanResources.
EmployeeAddress |
Description |
Cross-reference table mapping employees to their address(es). |
Column |
Data Type |
Nullable |
Default |
Description |
EmployeeID |
int |
not null |
|
Primary key. Foreign key to Employee.EmployeeID. |
AddressID |
int |
not null |
|
Primary key. Foreign key to Address.AddressID. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_EmployeeAddress_EmployeeID_AddressID |
EmployeeID, AddressID |
Index |
Type |
Columns |
AK_EmployeeAddress_rowguid |
Unique |
rowguid |
Relation |
Column |
Referenced Column |
Person.Address |
AddressID |
AddressID |
HumanResources.Employee |
EmployeeID |
EmployeeID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
HumanResources |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployee |
Select |
View |
Sales.
vSalesPerson |
Table |
HumanResources.
EmployeeDepartmentHistory |
Description |
Employee department transfers. |
Column |
Data Type |
Nullable |
Default |
Description |
EmployeeID |
int |
not null |
|
Employee identification number. Foreign key to Employee.EmployeeID. |
DepartmentID |
smallint |
not null |
|
Department in which the employee worked including currently. Foreign key to Department.DepartmentID. |
ShiftID |
tinyint |
not null |
|
Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. |
StartDate |
datetime |
not null |
|
Date the employee started work in the department. |
EndDate |
datetime |
null |
|
Date the employee left the department. NULL = Current department. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID |
EmployeeID, DepartmentID, ShiftID, StartDate |
Index |
Type |
Columns |
IX_EmployeeDepartmentHistory_DepartmentID |
|
DepartmentID |
IX_EmployeeDepartmentHistory_ShiftID |
|
ShiftID |
Check Constraint |
Expression |
Description |
CK_EmployeeDepartmentHistory_EndDate |
([EndDate]>=[StartDate] OR [EndDate] IS NULL) |
Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL |
Relation |
Column |
Referenced Column |
HumanResources.Department |
DepartmentID |
DepartmentID |
HumanResources.Employee |
EmployeeID |
EmployeeID |
HumanResources.Shift |
ShiftID |
ShiftID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
HumanResources |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployeeDepartment |
Select |
View |
HumanResources.
vEmployeeDepartmentHistory |
Table |
HumanResources.
EmployeePayHistory |
Description |
Employee pay history. |
Column |
Data Type |
Nullable |
Default |
Description |
EmployeeID |
int |
not null |
|
Employee identification number. Foreign key to Employee.EmployeeID. |
RateChangeDate |
datetime |
not null |
|
Date the change in pay is effective |
Rate |
money |
not null |
|
Salary hourly rate. |
PayFrequency |
tinyint |
not null |
|
1 = Salary received monthly, 2 = Salary received biweekly |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_EmployeePayHistory_EmployeeID_RateChangeDate |
EmployeeID, RateChangeDate |
Check Constraint |
Expression |
Description |
CK_EmployeePayHistory_PayFrequency |
([PayFrequency]=(2) OR [PayFrequency]=(1)) |
Check constraint [PayFrequency]=(3) OR [PayFrequency]=(2) OR [PayFrequency]=(1) |
CK_EmployeePayHistory_Rate |
([Rate]>=(6.50) AND [Rate]<=(200.00)) |
Check constraint [Rate] >= (6.50) AND [Rate] <= (200.00) |
Relation |
Column |
Referenced Column |
HumanResources.Employee |
EmployeeID |
EmployeeID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
HumanResources |
Reference Type |
Object Type |
Referencing Object |
Insert |
Procedure |
HumanResources.
uspUpdateEmployeeHireInfo |
Table |
dbo.
ErrorLog |
Description |
Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct. |
Column |
Data Type |
Nullable |
Default |
Description |
ErrorLogID |
int |
not null |
|
Primary key for ErrorLog records. |
ErrorTime |
datetime |
not null |
(getdate())
|
The date and time at which the error occurred. |
UserName |
sysname |
not null |
|
The user who executed the batch in which the error occurred. |
ErrorNumber |
int |
not null |
|
The error number of the error that occurred. |
ErrorSeverity |
int |
null |
|
The severity of the error that occurred. |
ErrorState |
int |
null |
|
The state number of the error that occurred. |
ErrorProcedure |
nvarchar(126) |
null |
|
The name of the stored procedure or trigger where the error occurred. |
ErrorLine |
int |
null |
|
The line number at which the error occurred. |
ErrorMessage |
nvarchar(4000) |
not null |
|
The message text of the error that occurred. |
Primary Key |
Columns |
PK_ErrorLog_ErrorLogID |
ErrorLogID |
Reference Type |
Object Type |
Referencing Object |
Insert |
Procedure |
dbo.
uspLogError |
Table |
Production.
Illustration |
Description |
Bicycle assembly diagrams. |
Column |
Data Type |
Nullable |
Default |
Description |
IllustrationID |
int |
not null |
|
Primary key for Illustration records. |
Diagram |
xml |
null |
|
Illustrations used in manufacturing instructions. Stored as XML. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Illustration_IllustrationID |
IllustrationID |
Detail Table |
Column |
Referencing Column |
Production.ProductModelIllustration |
IllustrationID |
IllustrationID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Table |
Sales.
Individual |
Description |
Demographic data about customers that purchase Adventure Works products online. |
Column |
Data Type |
Nullable |
Default |
Description |
CustomerID |
int |
not null |
|
Unique customer identification number. Foreign key to Customer.CustomerID. |
ContactID |
int |
not null |
|
Identifies the customer in the Contact table. Foreign key to Contact.ContactID. |
Demographics |
xml |
null |
|
Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Individual_CustomerID |
CustomerID |
Index |
Type |
Columns |
PXML_Individual_Demographics |
|
Demographics |
XMLPATH_Individual_Demographics |
|
Demographics |
XMLPROPERTY_Individual_Demographics |
|
Demographics |
XMLVALUE_Individual_Demographics |
|
Demographics |
Relation |
Column |
Referenced Column |
Person.Contact |
ContactID |
ContactID |
Sales.Customer |
CustomerID |
CustomerID |
Trigger |
Type |
iuIndividual |
ON INSERT UPDATE |
CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual] AFTER INSERT, UPDATE NOT FOR REPLICATION AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; -- Only allow the Customer to be a Store OR Individual IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store] ON inserted.[CustomerID] = [Sales].[Store].[CustomerID]) BEGIN -- Rollback any active or uncommittable transactions IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END END; IF UPDATE([CustomerID]) OR UPDATE([Demographics]) BEGIN UPDATE [Sales].[Individual] SET [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"> <TotalPurchaseYTD>0.00</TotalPurchaseYTD> </IndividualSurvey>' FROM inserted WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID] AND inserted.[Demographics] IS NULL; UPDATE [Sales].[Individual] SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> as first into (/IndividualSurvey)[1]') FROM inserted WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID] AND inserted.[Demographics] IS NOT NULL AND inserted.[Demographics].exist(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; /IndividualSurvey/TotalPurchaseYTD') <> 1; END; END;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Schema |
Schema |
Sales |
|
|
Data Type |
XML Schema Collection |
Sales.
IndividualSurveySchemaCollection |
|
|
Update |
Table |
Sales.
Individual |
Trigger |
iuIndividual |
Select |
Table |
Sales.
Store |
Trigger |
iuIndividual |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
View |
Sales.
vIndividualCustomer |
|
|
Select |
View |
Sales.
vIndividualDemographics |
|
|
Select |
SQL table-valued-function |
dbo.
ufnGetContactInformation |
|
|
Update |
Table |
Sales.
Individual |
Trigger |
iuIndividual |
Update |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Select |
Table |
Sales.
Store |
Trigger |
iStore |
Table |
HumanResources.
JobCandidate |
Description |
Résumés submitted to Human Resources by job applicants. |
Column |
Data Type |
Nullable |
Default |
Description |
JobCandidateID |
int |
not null |
|
Primary key for JobCandidate records. |
EmployeeID |
int |
null |
|
Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID. |
Resume |
xml |
null |
|
Résumé in XML format. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_JobCandidate_JobCandidateID |
JobCandidateID |
Index |
Type |
Columns |
IX_JobCandidate_EmployeeID |
|
EmployeeID |
Relation |
Column |
Referenced Column |
HumanResources.Employee |
EmployeeID |
EmployeeID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
HumanResources |
Data Type |
XML Schema Collection |
HumanResources.
HRResumeSchemaCollection |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vJobCandidate |
Select |
View |
HumanResources.
vJobCandidateEducation |
Select |
View |
HumanResources.
vJobCandidateEmployment |
Table |
Production.
Location |
Description |
Product inventory and manufacturing locations. |
Column |
Data Type |
Nullable |
Default |
Description |
LocationID |
smallint |
not null |
|
Primary key for Location records. |
Name |
dbo.Name |
not null |
|
Location description. |
CostRate |
smallmoney |
not null |
((0.00))
|
Standard hourly cost of the manufacturing location. |
Availability |
decimal(8, 2) |
not null |
((0.00))
|
Work capacity (in hours) of the manufacturing location. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Location_LocationID |
LocationID |
Index |
Type |
Columns |
AK_Location_Name |
Unique |
Name |
Check Constraint |
Expression |
Description |
CK_Location_Availability |
([Availability]>=(0.00)) |
Check constraint [Availability] >= (0.00) |
CK_Location_CostRate |
([CostRate]>=(0.00)) |
Check constraint [CostRate] >= (0.00) |
Detail Table |
Column |
Referencing Column |
Production.ProductInventory |
LocationID |
LocationID |
Production.WorkOrderRouting |
LocationID |
LocationID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Table |
Production.
Product |
Description |
Products sold or used in the manfacturing of sold products. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductID |
int |
not null |
|
Primary key for Product records. |
Name |
dbo.Name |
not null |
|
Name of the product. |
ProductNumber |
nvarchar(25) |
not null |
|
Unique product identification number. |
MakeFlag |
dbo.Flag |
not null |
((1))
|
0 = Product is purchased, 1 = Product is manufactured in-house. |
FinishedGoodsFlag |
dbo.Flag |
not null |
((1))
|
0 = Product is not a salable item. 1 = Product is salable. |
Color |
nvarchar(15) |
null |
|
Product color. |
SafetyStockLevel |
smallint |
not null |
|
Minimum inventory quantity. |
ReorderPoint |
smallint |
not null |
|
Inventory level that triggers a purchase order or work order. |
StandardCost |
money |
not null |
|
Standard cost of the product. |
ListPrice |
money |
not null |
|
Selling price. |
Size |
nvarchar(5) |
null |
|
Product size. |
SizeUnitMeasureCode |
nchar(3) |
null |
|
Unit of measure for Size column. |
WeightUnitMeasureCode |
nchar(3) |
null |
|
Unit of measure for Weight column. |
Weight |
decimal(8, 2) |
null |
|
Product weight. |
DaysToManufacture |
int |
not null |
|
Number of days required to manufacture the product. |
ProductLine |
nchar(2) |
null |
|
R = Road, M = Mountain, T = Touring, S = Standard |
Class |
nchar(2) |
null |
|
H = High, M = Medium, L = Low |
Style |
nchar(2) |
null |
|
W = Womens, M = Mens, U = Universal |
ProductSubcategoryID |
int |
null |
|
Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID. |
ProductModelID |
int |
null |
|
Product is a member of this product model. Foreign key to ProductModel.ProductModelID. |
SellStartDate |
datetime |
not null |
|
Date the product was available for sale. |
SellEndDate |
datetime |
null |
|
Date the product was no longer available for sale. |
DiscontinuedDate |
datetime |
null |
|
Date the product was discontinued. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Product_ProductID |
ProductID |
Index |
Type |
Columns |
AK_Product_ProductNumber |
Unique |
ProductNumber |
AK_Product_Name |
Unique |
Name |
AK_Product_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_Product_Class |
(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL) |
Check constraint [Class]='h' OR [Class]='m' OR [Class]='l' OR [Class]='H' OR [Class]='M' OR [Class]='L' OR [Class] IS NULL |
CK_Product_DaysToManufacture |
([DaysToManufacture]>=(0)) |
Check constraint [DaysToManufacture] >= (0) |
CK_Product_ListPrice |
([ListPrice]>=(0.00)) |
Check constraint [ListPrice] >= (0.00) |
CK_Product_ProductLine |
(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL) |
Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL |
CK_Product_ReorderPoint |
([ReorderPoint]>(0)) |
Check constraint [ReorderPoint] > (0) |
CK_Product_SafetyStockLevel |
([SafetyStockLevel]>(0)) |
Check constraint [SafetyStockLevel] > (0) |
CK_Product_SellEndDate |
([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL) |
Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL |
CK_Product_StandardCost |
([StandardCost]>=(0.00)) |
Check constraint [SafetyStockLevel] > (0) |
CK_Product_Style |
(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL) |
Check constraint [Style]='u' OR [Style]='m' OR [Style]='w' OR [Style]='U' OR [Style]='M' OR [Style]='W' OR [Style] IS NULL |
CK_Product_Weight |
([Weight]>(0.00)) |
Check constraint [Weight] > (0.00) |
Relation |
Column |
Referenced Column |
Production.ProductModel |
ProductModelID |
ProductModelID |
Production.ProductSubcategory |
ProductSubcategoryID |
ProductSubcategoryID |
Production.UnitMeasure |
SizeUnitMeasureCode |
UnitMeasureCode |
Production.UnitMeasure |
WeightUnitMeasureCode |
UnitMeasureCode |
Detail Table |
Column |
Referencing Column |
Production.BillOfMaterials |
ProductID |
ComponentID |
Production.BillOfMaterials |
ProductID |
ProductAssemblyID |
Production.ProductCostHistory |
ProductID |
ProductID |
Production.ProductDocument |
ProductID |
ProductID |
Production.ProductInventory |
ProductID |
ProductID |
Production.ProductListPriceHistory |
ProductID |
ProductID |
Production.ProductProductPhoto |
ProductID |
ProductID |
Production.ProductReview |
ProductID |
ProductID |
Purchasing.ProductVendor |
ProductID |
ProductID |
Purchasing.PurchaseOrderDetail |
ProductID |
ProductID |
Sales.ShoppingCartItem |
ProductID |
ProductID |
Sales.SpecialOfferProduct |
ProductID |
ProductID |
Production.TransactionHistory |
ProductID |
ProductID |
Production.WorkOrder |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Flag |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Production.
vProductAndDescription |
Select |
Procedure |
dbo.
uspGetBillOfMaterials |
Select |
Procedure |
dbo.
uspGetWhereUsedProductID |
Select |
Function |
dbo.
ufnGetProductDealerPrice |
Select |
Function |
dbo.
ufnGetProductListPrice |
Select |
Function |
dbo.
ufnGetProductStandardCost |
Table |
Production.
ProductCategory |
Description |
High-level product categorization. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductCategoryID |
int |
not null |
|
Primary key for ProductCategory records. |
Name |
dbo.Name |
not null |
|
Category description. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductCategory_ProductCategoryID |
ProductCategoryID |
Index |
Type |
Columns |
AK_ProductCategory_Name |
Unique |
Name |
AK_ProductCategory_rowguid |
Unique |
rowguid |
Detail Table |
Column |
Referencing Column |
Production.ProductSubcategory |
ProductCategoryID |
ProductCategoryID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Table |
Production.
ProductCostHistory |
Description |
Changes in the cost of a product over time. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID |
StartDate |
datetime |
not null |
|
Product cost start date. |
EndDate |
datetime |
null |
|
Product cost end date. |
StandardCost |
money |
not null |
|
Standard cost of the product. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductCostHistory_ProductID_StartDate |
ProductID, StartDate |
Check Constraint |
Expression |
Description |
CK_ProductCostHistory_EndDate |
([EndDate]>=[StartDate] OR [EndDate] IS NULL) |
Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL |
CK_ProductCostHistory_StandardCost |
([StandardCost]>=(0.00)) |
Check constraint [StandardCost] >= (0.00) |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Select |
Function |
dbo.
ufnGetProductStandardCost |
Table |
Production.
ProductDescription |
Description |
Product descriptions in several languages. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductDescriptionID |
int |
not null |
|
Primary key for ProductDescription records. |
Description |
nvarchar(400) |
not null |
|
Description of the product. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductDescription_ProductDescriptionID |
ProductDescriptionID |
Index |
Type |
Columns |
AK_ProductDescription_rowguid |
Unique |
rowguid |
Detail Table |
Column |
Referencing Column |
Production.ProductModelProductDescriptionCulture |
ProductDescriptionID |
ProductDescriptionID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Production.
vProductAndDescription |
Table |
Production.
ProductDocument |
Description |
Cross-reference table mapping products to related product documents. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
DocumentID |
int |
not null |
|
Document identification number. Foreign key to Document.DocumentID. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductDocument_ProductID_DocumentID |
ProductID, DocumentID |
Relation |
Column |
Referenced Column |
Production.Document |
DocumentID |
DocumentID |
Production.Product |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Table |
Production.
ProductInventory |
Description |
Product inventory information. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
LocationID |
smallint |
not null |
|
Inventory location identification number. Foreign key to Location.LocationID. |
Shelf |
nvarchar(10) |
not null |
|
Storage compartment within an inventory location. |
Bin |
tinyint |
not null |
|
Storage container on a shelf in an inventory location. |
Quantity |
smallint |
not null |
((0))
|
Quantity of products in the inventory location. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductInventory_ProductID_LocationID |
ProductID, LocationID |
Check Constraint |
Expression |
Description |
CK_ProductInventory_Bin |
([Bin]>=(0) AND [Bin]<=(100)) |
Check constraint [Bin] BETWEEN (0) AND (100) |
CK_ProductInventory_Shelf |
([Shelf] like '[A-Za-z]' OR [Shelf]='N/A') |
Check constraint [Shelf] like '[A-Za-z]' OR [Shelf]='N/A' |
Relation |
Column |
Referenced Column |
Production.Location |
LocationID |
LocationID |
Production.Product |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Select |
Function |
dbo.
ufnGetStock |
Table |
Production.
ProductListPriceHistory |
Description |
Changes in the list price of a product over time. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID |
StartDate |
datetime |
not null |
|
List price start date. |
EndDate |
datetime |
null |
|
List price end date |
ListPrice |
money |
not null |
|
Product list price. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductListPriceHistory_ProductID_StartDate |
ProductID, StartDate |
Check Constraint |
Expression |
Description |
CK_ProductListPriceHistory_EndDate |
([EndDate]>=[StartDate] OR [EndDate] IS NULL) |
Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL |
CK_ProductListPriceHistory_ListPrice |
([ListPrice]>(0.00)) |
Check constraint [ListPrice] > (0.00) |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Select |
Function |
dbo.
ufnGetProductDealerPrice |
Select |
Function |
dbo.
ufnGetProductListPrice |
Table |
Production.
ProductModel |
Description |
Product model classification. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductModelID |
int |
not null |
|
Primary key for ProductModel records. |
Name |
dbo.Name |
not null |
|
Product model description. |
CatalogDescription |
xml |
null |
|
Detailed product catalog information in xml format. |
Instructions |
xml |
null |
|
Manufacturing instructions in xml format. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductModel_ProductModelID |
ProductModelID |
Index |
Type |
Columns |
AK_ProductModel_Name |
Unique |
Name |
AK_ProductModel_rowguid |
Unique |
rowguid |
PXML_ProductModel_CatalogDescription |
|
CatalogDescription |
PXML_ProductModel_Instructions |
|
Instructions |
Detail Table |
Column |
Referencing Column |
Production.Product |
ProductModelID |
ProductModelID |
Production.ProductModelIllustration |
ProductModelID |
ProductModelID |
Production.ProductModelProductDescriptionCulture |
ProductModelID |
ProductModelID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Data Type |
XML Schema Collection |
Production.
ManuInstructionsSchemaCollection |
Data Type |
XML Schema Collection |
Production.
ProductDescriptionSchemaCollection |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Production.
vProductAndDescription |
Select |
View |
Production.
vProductModelCatalogDescription |
Select |
View |
Production.
vProductModelInstructions |
Table |
Production.
ProductModelIllustration |
Description |
Cross-reference table mapping product models and illustrations. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductModelID |
int |
not null |
|
Primary key. Foreign key to ProductModel.ProductModelID. |
IllustrationID |
int |
not null |
|
Primary key. Foreign key to Illustration.IllustrationID. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductModelIllustration_ProductModelID_IllustrationID |
ProductModelID, IllustrationID |
Relation |
Column |
Referenced Column |
Production.Illustration |
IllustrationID |
IllustrationID |
Production.ProductModel |
ProductModelID |
ProductModelID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Table |
Production.
ProductModelProductDescriptionCulture |
Description |
Cross-reference table mapping product descriptions and the language the description is written in. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductModelID |
int |
not null |
|
Primary key. Foreign key to ProductModel.ProductModelID. |
ProductDescriptionID |
int |
not null |
|
Primary key. Foreign key to ProductDescription.ProductDescriptionID. |
CultureID |
nchar(6) |
not null |
|
Culture identification number. Foreign key to Culture.CultureID. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID |
ProductModelID, ProductDescriptionID, CultureID |
Relation |
Column |
Referenced Column |
Production.Culture |
CultureID |
CultureID |
Production.ProductDescription |
ProductDescriptionID |
ProductDescriptionID |
Production.ProductModel |
ProductModelID |
ProductModelID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Production.
vProductAndDescription |
Table |
Production.
ProductPhoto |
Description |
Product images. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductPhotoID |
int |
not null |
|
Primary key for ProductPhoto records. |
ThumbNailPhoto |
varbinary(max) |
null |
|
Small image of the product. |
ThumbnailPhotoFileName |
nvarchar(50) |
null |
|
Small image file name. |
LargePhoto |
varbinary(max) |
null |
|
Large image of the product. |
LargePhotoFileName |
nvarchar(50) |
null |
|
Large image file name. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductPhoto_ProductPhotoID |
ProductPhotoID |
Detail Table |
Column |
Referencing Column |
Production.ProductProductPhoto |
ProductPhotoID |
ProductPhotoID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Table |
Production.
ProductProductPhoto |
Description |
Cross-reference table mapping products and product photos. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
ProductPhotoID |
int |
not null |
|
Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID. |
Primary |
dbo.Flag |
not null |
((0))
|
0 = Photo is not the principal image. 1 = Photo is the principal image. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductProductPhoto_ProductID_ProductPhotoID |
ProductID, ProductPhotoID |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Production.ProductPhoto |
ProductPhotoID |
ProductPhotoID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Flag |
Schema |
Schema |
Production |
Table |
Production.
ProductReview |
Description |
Customer reviews of products they have purchased. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductReviewID |
int |
not null |
|
Primary key for ProductReview records. |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
ReviewerName |
dbo.Name |
not null |
|
Name of the reviewer. |
ReviewDate |
datetime |
not null |
(getdate())
|
Date review was submitted. |
EmailAddress |
nvarchar(50) |
not null |
|
Reviewer's e-mail address. |
Rating |
int |
not null |
|
Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating. |
Comments |
nvarchar(3850) |
null |
|
Reviewer's comments |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductReview_ProductReviewID |
ProductReviewID |
Index |
Type |
Columns |
IX_ProductReview_ProductID_Name |
|
ProductID, ReviewerName, Comments |
Check Constraint |
Expression |
Description |
CK_ProductReview_Rating |
([Rating]>=(1) AND [Rating]<=(5)) |
Check constraint [Rating] BETWEEN (1) AND (5) |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Table |
Production.
ProductSubcategory |
Description |
Product subcategories. See ProductCategory table. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductSubcategoryID |
int |
not null |
|
Primary key for ProductSubcategory records. |
ProductCategoryID |
int |
not null |
|
Product category identification number. Foreign key to ProductCategory.ProductCategoryID. |
Name |
dbo.Name |
not null |
|
Subcategory description. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductSubcategory_ProductSubcategoryID |
ProductSubcategoryID |
Index |
Type |
Columns |
AK_ProductSubcategory_Name |
Unique |
Name |
AK_ProductSubcategory_rowguid |
Unique |
rowguid |
Relation |
Column |
Referenced Column |
Production.ProductCategory |
ProductCategoryID |
ProductCategoryID |
Detail Table |
Column |
Referencing Column |
Production.Product |
ProductSubcategoryID |
ProductSubcategoryID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Table |
Purchasing.
ProductVendor |
Description |
Cross-reference table mapping vendors with the products they supply. |
Column |
Data Type |
Nullable |
Default |
Description |
ProductID |
int |
not null |
|
Primary key. Foreign key to Product.ProductID. |
VendorID |
int |
not null |
|
Primary key. Foreign key to Vendor.VendorID. |
AverageLeadTime |
int |
not null |
|
The average span of time (in days) between placing an order with the vendor and receiving the purchased product. |
StandardPrice |
money |
not null |
|
The vendor's usual selling price. |
LastReceiptCost |
money |
null |
|
The selling price when last purchased. |
LastReceiptDate |
datetime |
null |
|
Date the product was last received by the vendor. |
MinOrderQty |
int |
not null |
|
The maximum quantity that should be ordered. |
MaxOrderQty |
int |
not null |
|
The minimum quantity that should be ordered. |
OnOrderQty |
int |
null |
|
The quantity currently on order. |
UnitMeasureCode |
nchar(3) |
not null |
|
The product's unit of measure. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ProductVendor_ProductID_VendorID |
ProductID, VendorID |
Index |
Type |
Columns |
IX_ProductVendor_UnitMeasureCode |
|
UnitMeasureCode |
IX_ProductVendor_VendorID |
|
VendorID |
Check Constraint |
Expression |
Description |
CK_ProductVendor_AverageLeadTime |
([AverageLeadTime]>=(1)) |
Check constraint [AverageLeadTime] >= (1) |
CK_ProductVendor_LastReceiptCost |
([LastReceiptCost]>(0.00)) |
Check constraint [LastReceiptCost] > (0.00) |
CK_ProductVendor_MaxOrderQty |
([MaxOrderQty]>=(1)) |
Check constraint [MaxOrderQty] >= (1) |
CK_ProductVendor_MinOrderQty |
([MinOrderQty]>=(1)) |
Check constraint [MinOrderQty] >= (1) |
CK_ProductVendor_OnOrderQty |
([OnOrderQty]>=(0)) |
Check constraint [OnOrderQty] >= (0) |
CK_ProductVendor_StandardPrice |
([StandardPrice]>(0.00)) |
Check constraint [StandardPrice] > (0.00) |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Production.UnitMeasure |
UnitMeasureCode |
UnitMeasureCode |
Purchasing.Vendor |
VendorID |
VendorID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Purchasing |
Table |
Purchasing.
PurchaseOrderDetail |
Description |
Individual products associated with a specific purchase order. See PurchaseOrderHeader. |
Column |
Data Type |
Nullable |
Default |
Description |
PurchaseOrderID |
int |
not null |
|
Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID. |
PurchaseOrderDetailID |
int |
not null |
|
Primary key. One line number per purchased product. |
DueDate |
datetime |
not null |
|
Date the product is expected to be received. |
OrderQty |
smallint |
not null |
|
Quantity ordered. |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
UnitPrice |
money |
not null |
|
Vendor's selling price of a single product. |
LineTotal |
|
|
|
Per product subtotal. Computed as OrderQty * UnitPrice. |
ReceivedQty |
decimal(8, 2) |
not null |
|
Quantity actually received from the vendor. |
RejectedQty |
decimal(8, 2) |
not null |
|
Quantity rejected during inspection. |
StockedQty |
|
|
|
Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID |
PurchaseOrderID, PurchaseOrderDetailID |
Index |
Type |
Columns |
IX_PurchaseOrderDetail_ProductID |
|
ProductID |
Check Constraint |
Expression |
Description |
CK_PurchaseOrderDetail_OrderQty |
([OrderQty]>(0)) |
Check constraint [OrderQty] > (0) |
CK_PurchaseOrderDetail_ReceivedQty |
([ReceivedQty]>=(0.00)) |
Check constraint [ReceivedQty] >= (0.00) |
CK_PurchaseOrderDetail_RejectedQty |
([RejectedQty]>=(0.00)) |
Check constraint [RejectedQty] >= (0.00) |
CK_PurchaseOrderDetail_UnitPrice |
([UnitPrice]>=(0.00)) |
Check constraint [UnitPrice] >= (0.00) |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Purchasing.PurchaseOrderHeader |
PurchaseOrderID |
PurchaseOrderID |
Trigger |
Type |
iPurchaseOrderDetail |
ON INSERT |
uPurchaseOrderDetail |
ON UPDATE |
CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderHeader] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
CREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) -- Insert record into TransactionHistory BEGIN INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderDetail] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); UPDATE [Purchasing].[PurchaseOrderDetail] SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID] AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID]; END; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Schema |
Schema |
Purchasing |
|
|
Insert |
Table |
Production.
TransactionHistory |
Trigger |
iPurchaseOrderDetail |
Insert |
Table |
Production.
TransactionHistory |
Trigger |
uPurchaseOrderDetail |
Select |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Select |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
iPurchaseOrderDetail |
Update |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Select |
Table |
Purchasing.
PurchaseOrderHeader |
Trigger |
iPurchaseOrderDetail |
Update |
Table |
Purchasing.
PurchaseOrderHeader |
Trigger |
iPurchaseOrderDetail |
Update |
Table |
Purchasing.
PurchaseOrderHeader |
Trigger |
uPurchaseOrderDetail |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
iPurchaseOrderDetail |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
uPurchaseOrderDetail |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
uPurchaseOrderDetail |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
iPurchaseOrderDetail |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
iPurchaseOrderDetail |
Select |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Update |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Table |
Purchasing.
PurchaseOrderHeader |
Description |
General purchase order information. See PurchaseOrderDetail. |
Column |
Data Type |
Nullable |
Default |
Description |
PurchaseOrderID |
int |
not null |
|
Primary key. |
RevisionNumber |
tinyint |
not null |
((0))
|
Incremental number to track changes to the purchase order over time. |
Status |
tinyint |
not null |
((1))
|
Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete |
EmployeeID |
int |
not null |
|
Employee who created the purchase order. Foreign key to Employee.EmployeeID. |
VendorID |
int |
not null |
|
Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID. |
ShipMethodID |
int |
not null |
|
Shipping method. Foreign key to ShipMethod.ShipMethodID. |
OrderDate |
datetime |
not null |
(getdate())
|
Purchase order creation date. |
ShipDate |
datetime |
null |
|
Estimated shipment date from the vendor. |
SubTotal |
money |
not null |
((0.00))
|
Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID. |
TaxAmt |
money |
not null |
((0.00))
|
Tax amount. |
Freight |
money |
not null |
((0.00))
|
Shipping cost. |
TotalDue |
|
|
|
Total due to vendor. Computed as Subtotal + TaxAmt + Freight. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_PurchaseOrderHeader_PurchaseOrderID |
PurchaseOrderID |
Index |
Type |
Columns |
IX_PurchaseOrderHeader_VendorID |
|
VendorID |
IX_PurchaseOrderHeader_EmployeeID |
|
EmployeeID |
Check Constraint |
Expression |
Description |
CK_PurchaseOrderHeader_Freight |
([Freight]>=(0.00)) |
Check constraint [Freight] >= (0.00) |
CK_PurchaseOrderHeader_ShipDate |
([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) |
Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL |
CK_PurchaseOrderHeader_Status |
([Status]>=(1) AND [Status]<=(4)) |
Check constraint [Status] BETWEEN (1) AND (4) |
CK_PurchaseOrderHeader_SubTotal |
([SubTotal]>=(0.00)) |
Check constraint [SubTotal] >= (0.00) |
CK_PurchaseOrderHeader_TaxAmt |
([TaxAmt]>=(0.00)) |
Check constraint [TaxAmt] >= (0.00) |
Relation |
Column |
Referenced Column |
HumanResources.Employee |
EmployeeID |
EmployeeID |
Purchasing.ShipMethod |
ShipMethodID |
ShipMethodID |
Purchasing.Vendor |
VendorID |
VendorID |
Detail Table |
Column |
Referencing Column |
Purchasing.PurchaseOrderDetail |
PurchaseOrderID |
PurchaseOrderID |
Trigger |
Type |
uPurchaseOrderHeader |
ON UPDATE |
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY -- Update RevisionNumber for modification of any field EXCEPT the Status. IF NOT UPDATE([Status]) BEGIN UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] = [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1 WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); END; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Schema |
Schema |
Purchasing |
|
|
Update |
Table |
Purchasing.
PurchaseOrderHeader |
Trigger |
uPurchaseOrderHeader |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
uPurchaseOrderHeader |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
uPurchaseOrderHeader |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
iPurchaseOrderDetail |
Update |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
iPurchaseOrderDetail |
Update |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Update |
Table |
Purchasing.
PurchaseOrderHeader |
Trigger |
uPurchaseOrderHeader |
Table |
Sales.
SalesOrderDetail |
Description |
Individual products associated with a specific sales order. See SalesOrderHeader. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesOrderID |
int |
not null |
|
Primary key. Foreign key to SalesOrderHeader.SalesOrderID. |
SalesOrderDetailID |
int |
not null |
|
Primary key. One incremental unique number per product sold. |
CarrierTrackingNumber |
nvarchar(25) |
null |
|
Shipment tracking number supplied by the shipper. |
OrderQty |
smallint |
not null |
|
Quantity ordered per product. |
ProductID |
int |
not null |
|
Product sold to customer. Foreign key to Product.ProductID. |
SpecialOfferID |
int |
not null |
|
Promotional code. Foreign key to SpecialOffer.SpecialOfferID. |
UnitPrice |
money |
not null |
|
Selling price of a single product. |
UnitPriceDiscount |
money |
not null |
((0.0))
|
Discount amount. |
LineTotal |
|
|
|
Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID |
SalesOrderID, SalesOrderDetailID |
Index |
Type |
Columns |
AK_SalesOrderDetail_rowguid |
Unique |
rowguid |
IX_SalesOrderDetail_ProductID |
|
ProductID |
Check Constraint |
Expression |
Description |
CK_SalesOrderDetail_OrderQty |
([OrderQty]>(0)) |
Check constraint [OrderQty] > (0) |
CK_SalesOrderDetail_UnitPrice |
([UnitPrice]>=(0.00)) |
Check constraint [UnitPrice] >= (0.00) |
CK_SalesOrderDetail_UnitPriceDiscount |
([UnitPriceDiscount]>=(0.00)) |
Check constraint [UnitPriceDiscount] >= (0.00) |
Relation |
Column |
Referenced Column |
Sales.SalesOrderHeader |
SalesOrderID |
SalesOrderID |
Sales.SpecialOfferProduct |
SpecialOfferID |
SpecialOfferID |
Trigger |
Type |
iduSalesOrderDetail |
ON INSERT UPDATE DELETE |
CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail] AFTER INSERT, DELETE, UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY -- If inserting or updating these columns IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount]) -- Insert record into TransactionHistory BEGIN INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[SalesOrderID] ,inserted.[SalesOrderDetailID] ,'S' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Sales].[SalesOrderHeader] ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]; UPDATE [Sales].[Individual] SET [Demographics].modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")') FROM inserted INNER JOIN [Sales].[SalesOrderHeader] ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; END; -- Update SubTotal in SalesOrderHeader record. Note that this causes the -- SalesOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Sales].[SalesOrderHeader] SET [Sales].[SalesOrderHeader].[SubTotal] = (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal]) FROM [Sales].[SalesOrderDetail] WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]) WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted); UPDATE [Sales].[Individual] SET [Demographics].modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")') FROM deleted INNER JOIN [Sales].[SalesOrderHeader] ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Schema |
Schema |
Sales |
|
|
Insert |
Table |
Production.
TransactionHistory |
Trigger |
iduSalesOrderDetail |
Update |
Table |
Sales.
Individual |
Trigger |
iduSalesOrderDetail |
Select |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Select |
Table |
Sales.
SalesOrderHeader |
Trigger |
iduSalesOrderDetail |
Update |
Table |
Sales.
SalesOrderHeader |
Trigger |
iduSalesOrderDetail |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
iduSalesOrderDetail |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
iduSalesOrderDetail |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Table |
Sales.
SalesOrderHeader |
Description |
General sales order information. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesOrderID |
int |
not null |
|
Primary key. |
RevisionNumber |
tinyint |
not null |
((0))
|
Incremental number to track changes to the sales order over time. |
OrderDate |
datetime |
not null |
(getdate())
|
Dates the sales order was created. |
DueDate |
datetime |
not null |
|
Date the order is due to the customer. |
ShipDate |
datetime |
null |
|
Date the order was shipped to the customer. |
Status |
tinyint |
not null |
((1))
|
Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled |
OnlineOrderFlag |
dbo.Flag |
not null |
((1))
|
0 = Order placed by sales person. 1 = Order placed online by customer. |
SalesOrderNumber |
|
|
|
Unique sales order identification number. |
PurchaseOrderNumber |
dbo.OrderNumber |
null |
|
Customer purchase order number reference. |
AccountNumber |
dbo.AccountNumber |
null |
|
Financial accounting number reference. |
CustomerID |
int |
not null |
|
Customer identification number. Foreign key to Customer.CustomerID. |
ContactID |
int |
not null |
|
Customer contact identification number. Foreign key to Contact.ContactID. |
SalesPersonID |
int |
null |
|
Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID. |
TerritoryID |
int |
null |
|
Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID. |
BillToAddressID |
int |
not null |
|
Customer billing address. Foreign key to Address.AddressID. |
ShipToAddressID |
int |
not null |
|
Customer shipping address. Foreign key to Address.AddressID. |
ShipMethodID |
int |
not null |
|
Shipping method. Foreign key to ShipMethod.ShipMethodID. |
CreditCardID |
int |
null |
|
Credit card identification number. Foreign key to CreditCard.CreditCardID. |
CreditCardApprovalCode |
varchar(15) |
null |
|
Approval code provided by the credit card company. |
CurrencyRateID |
int |
null |
|
Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID. |
SubTotal |
money |
not null |
((0.00))
|
Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID. |
TaxAmt |
money |
not null |
((0.00))
|
Tax amount. |
Freight |
money |
not null |
((0.00))
|
Shipping cost. |
TotalDue |
|
|
|
Total due from customer. Computed as Subtotal + TaxAmt + Freight. |
Comment |
nvarchar(128) |
null |
|
Sales representative comments. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesOrderHeader_SalesOrderID |
SalesOrderID |
Index |
Type |
Columns |
AK_SalesOrderHeader_rowguid |
Unique |
rowguid |
AK_SalesOrderHeader_SalesOrderNumber |
Unique |
SalesOrderNumber |
IX_SalesOrderHeader_CustomerID |
|
CustomerID |
IX_SalesOrderHeader_SalesPersonID |
|
SalesPersonID |
Check Constraint |
Expression |
Description |
CK_SalesOrderHeader_DueDate |
([DueDate]>=[OrderDate]) |
Check constraint [DueDate] >= [OrderDate] |
CK_SalesOrderHeader_Freight |
([Freight]>=(0.00)) |
Check constraint [Freight] >= (0.00) |
CK_SalesOrderHeader_ShipDate |
([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) |
Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL |
CK_SalesOrderHeader_Status |
([Status]>=(0) AND [Status]<=(8)) |
Check constraint [Status] BETWEEN (0) AND (8) |
CK_SalesOrderHeader_SubTotal |
([SubTotal]>=(0.00)) |
Check constraint [SubTotal] >= (0.00) |
CK_SalesOrderHeader_TaxAmt |
([TaxAmt]>=(0.00)) |
Check constraint [TaxAmt] >= (0.00) |
Relation |
Column |
Referenced Column |
Person.Address |
BillToAddressID |
AddressID |
Person.Address |
ShipToAddressID |
AddressID |
Person.Contact |
ContactID |
ContactID |
Sales.CreditCard |
CreditCardID |
CreditCardID |
Sales.CurrencyRate |
CurrencyRateID |
CurrencyRateID |
Sales.Customer |
CustomerID |
CustomerID |
Sales.SalesPerson |
SalesPersonID |
SalesPersonID |
Sales.SalesTerritory |
TerritoryID |
TerritoryID |
Purchasing.ShipMethod |
ShipMethodID |
ShipMethodID |
Detail Table |
Column |
Referencing Column |
Sales.SalesOrderDetail |
SalesOrderID |
SalesOrderID |
Sales.SalesOrderHeaderSalesReason |
SalesOrderID |
SalesOrderID |
Trigger |
Type |
uSalesOrderHeader |
ON UPDATE |
CREATE TRIGGER [Sales].[uSalesOrderHeader] ON [Sales].[SalesOrderHeader] AFTER UPDATE NOT FOR REPLICATION AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY -- Update RevisionNumber for modification of any field EXCEPT the Status. IF NOT UPDATE([Status]) BEGIN UPDATE [Sales].[SalesOrderHeader] SET [Sales].[SalesOrderHeader].[RevisionNumber] = [Sales].[SalesOrderHeader].[RevisionNumber] + 1 WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted); END; -- Update the SalesPerson SalesYTD when SubTotal is updated IF UPDATE([SubTotal]) BEGIN DECLARE @StartDate datetime, @EndDate datetime SET @StartDate = [dbo].[ufnGetAccountingStartDate](); SET @EndDate = [dbo].[ufnGetAccountingEndDate](); UPDATE [Sales].[SalesPerson] SET [Sales].[SalesPerson].[SalesYTD] = (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal]) FROM [Sales].[SalesOrderHeader] WHERE [Sales].[SalesPerson].[SalesPersonID] = [Sales].[SalesOrderHeader].[SalesPersonID] AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate) WHERE [Sales].[SalesPerson].[SalesPersonID] IN (SELECT DISTINCT inserted.[SalesPersonID] FROM inserted WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate); -- Update the SalesTerritory SalesYTD when SubTotal is updated UPDATE [Sales].[SalesTerritory] SET [Sales].[SalesTerritory].[SalesYTD] = (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal]) FROM [Sales].[SalesOrderHeader] WHERE [Sales].[SalesTerritory].[TerritoryID] = [Sales].[SalesOrderHeader].[TerritoryID] AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate) WHERE [Sales].[SalesTerritory].[TerritoryID] IN (SELECT DISTINCT inserted.[TerritoryID] FROM inserted WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate); END; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Data Type |
Type |
dbo.
AccountNumber |
|
|
Data Type |
Type |
dbo.
Flag |
|
|
Data Type |
Type |
dbo.
OrderNumber |
|
|
Schema |
Schema |
Sales |
|
|
Select |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Update |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Update |
Table |
Sales.
SalesPerson |
Trigger |
uSalesOrderHeader |
Update |
Table |
Sales.
SalesTerritory |
Trigger |
uSalesOrderHeader |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
uSalesOrderHeader |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
uSalesOrderHeader |
Execute |
Function |
dbo.
ufnGetAccountingEndDate |
Trigger |
uSalesOrderHeader |
Execute |
Function |
dbo.
ufnGetAccountingStartDate |
Trigger |
uSalesOrderHeader |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
View |
Sales.
vSalesPersonSalesByFiscalYears |
|
|
Select |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Update |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Select |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Update |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Table |
Sales.
SalesOrderHeaderSalesReason |
Description |
Cross-reference table mapping sales orders to sales reason codes. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesOrderID |
int |
not null |
|
Primary key. Foreign key to SalesOrderHeader.SalesOrderID. |
SalesReasonID |
int |
not null |
|
Primary key. Foreign key to SalesReason.SalesReasonID. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID |
SalesOrderID, SalesReasonID |
Relation |
Column |
Referenced Column |
Sales.SalesOrderHeader |
SalesOrderID |
SalesOrderID |
Sales.SalesReason |
SalesReasonID |
SalesReasonID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Sales.
SalesPerson |
Description |
Sales representative current information. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesPersonID |
int |
not null |
|
Primary key for SalesPerson records. |
TerritoryID |
int |
null |
|
Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID. |
SalesQuota |
money |
null |
|
Projected yearly sales. |
Bonus |
money |
not null |
((0.00))
|
Bonus due if quota is met. |
CommissionPct |
smallmoney |
not null |
((0.00))
|
Commision percent received per sale. |
SalesYTD |
money |
not null |
((0.00))
|
Sales total year to date. |
SalesLastYear |
money |
not null |
((0.00))
|
Sales total of previous year. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesPerson_SalesPersonID |
SalesPersonID |
Index |
Type |
Columns |
AK_SalesPerson_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_SalesPerson_Bonus |
([Bonus]>=(0.00)) |
Check constraint [Bonus] >= (0.00) |
CK_SalesPerson_CommissionPct |
([CommissionPct]>=(0.00)) |
Check constraint [CommissionPct] >= (0.00) |
CK_SalesPerson_SalesLastYear |
([SalesLastYear]>=(0.00)) |
Check constraint [SalesLastYear] >= (0.00) |
CK_SalesPerson_SalesQuota |
([SalesQuota]>(0.00)) |
Check constraint [SalesQuota] > (0.00) |
CK_SalesPerson_SalesYTD |
([SalesYTD]>=(0.00)) |
Check constraint [SalesYTD] >= (0.00) |
Relation |
Column |
Referenced Column |
HumanResources.Employee |
SalesPersonID |
EmployeeID |
Sales.SalesTerritory |
TerritoryID |
TerritoryID |
Detail Table |
Column |
Referencing Column |
Sales.SalesOrderHeader |
SalesPersonID |
SalesPersonID |
Sales.SalesPersonQuotaHistory |
SalesPersonID |
SalesPersonID |
Sales.SalesTerritoryHistory |
SalesPersonID |
SalesPersonID |
Sales.Store |
SalesPersonID |
SalesPersonID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
View |
Sales.
vSalesPerson |
|
|
Select |
View |
Sales.
vSalesPersonSalesByFiscalYears |
|
|
Update |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Table |
Sales.
SalesPersonQuotaHistory |
Description |
Sales performance tracking. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesPersonID |
int |
not null |
|
Sales person identification number. Foreign key to SalesPerson.SalesPersonID. |
QuotaDate |
datetime |
not null |
|
Sales quota date. |
SalesQuota |
money |
not null |
|
Sales quota amount. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate |
SalesPersonID, QuotaDate |
Index |
Type |
Columns |
AK_SalesPersonQuotaHistory_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_SalesPersonQuotaHistory_SalesQuota |
([SalesQuota]>(0.00)) |
Check constraint [SalesQuota] > (0.00) |
Relation |
Column |
Referenced Column |
Sales.SalesPerson |
SalesPersonID |
SalesPersonID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Sales.
SalesReason |
Description |
Lookup table of customer purchase reasons. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesReasonID |
int |
not null |
|
Primary key for SalesReason records. |
Name |
dbo.Name |
not null |
|
Sales reason description. |
ReasonType |
dbo.Name |
not null |
|
Category the sales reason belongs to. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesReason_SalesReasonID |
SalesReasonID |
Detail Table |
Column |
Referencing Column |
Sales.SalesOrderHeaderSalesReason |
SalesReasonID |
SalesReasonID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Sales |
Table |
Sales.
SalesTaxRate |
Description |
Tax rate lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesTaxRateID |
int |
not null |
|
Primary key for SalesTaxRate records. |
StateProvinceID |
int |
not null |
|
State, province, or country/region the sales tax applies to. |
TaxType |
tinyint |
not null |
|
1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and wholesale) transactions. |
TaxRate |
smallmoney |
not null |
((0.00))
|
Tax rate amount. |
Name |
dbo.Name |
not null |
|
Tax rate description. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesTaxRate_SalesTaxRateID |
SalesTaxRateID |
Index |
Type |
Columns |
AK_SalesTaxRate_StateProvinceID_TaxType |
Unique |
StateProvinceID, TaxType |
AK_SalesTaxRate_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_SalesTaxRate_TaxType |
([TaxType]>=(1) AND [TaxType]<=(3)) |
Check constraint [TaxType] BETWEEN (1) AND (3) |
Relation |
Column |
Referenced Column |
Person.StateProvince |
StateProvinceID |
StateProvinceID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Sales |
Table |
Sales.
SalesTerritory |
Description |
Sales territory lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
TerritoryID |
int |
not null |
|
Primary key for SalesTerritory records. |
Name |
dbo.Name |
not null |
|
Sales territory description |
CountryRegionCode |
nvarchar(3) |
not null |
|
ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. |
Group |
nvarchar(50) |
not null |
|
Geographic area to which the sales territory belong. |
SalesYTD |
money |
not null |
((0.00))
|
Sales in the territory year to date. |
SalesLastYear |
money |
not null |
((0.00))
|
Sales in the territory the previous year. |
CostYTD |
money |
not null |
((0.00))
|
Business costs in the territory year to date. |
CostLastYear |
money |
not null |
((0.00))
|
Business costs in the territory the previous year. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesTerritory_TerritoryID |
TerritoryID |
Index |
Type |
Columns |
AK_SalesTerritory_Name |
Unique |
Name |
AK_SalesTerritory_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_SalesTerritory_CostLastYear |
([CostLastYear]>=(0.00)) |
Check constraint [CostLastYear] >= (0.00) |
CK_SalesTerritory_CostYTD |
([CostYTD]>=(0.00)) |
Check constraint [CostYTD] >= (0.00) |
CK_SalesTerritory_SalesLastYear |
([SalesLastYear]>=(0.00)) |
Check constraint [SalesLastYear] >= (0.00) |
CK_SalesTerritory_SalesYTD |
([SalesYTD]>=(0.00)) |
Check constraint [SalesYTD] >= (0.00) |
Detail Table |
Column |
Referencing Column |
Sales.Customer |
TerritoryID |
TerritoryID |
Sales.SalesOrderHeader |
TerritoryID |
TerritoryID |
Sales.SalesPerson |
TerritoryID |
TerritoryID |
Sales.SalesTerritoryHistory |
TerritoryID |
TerritoryID |
Person.StateProvince |
TerritoryID |
TerritoryID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Sales |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
View |
Sales.
vSalesPerson |
|
|
Select |
View |
Sales.
vSalesPersonSalesByFiscalYears |
|
|
Update |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Table |
Sales.
SalesTerritoryHistory |
Description |
Sales representative transfers to other sales territories. |
Column |
Data Type |
Nullable |
Default |
Description |
SalesPersonID |
int |
not null |
|
Primary key for SalesTerritoryHistory records. |
TerritoryID |
int |
not null |
|
Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID. |
StartDate |
datetime |
not null |
|
Date the sales representive started work in the territory. |
EndDate |
datetime |
null |
|
Date the sales representative left work in the territory. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID |
SalesPersonID, TerritoryID, StartDate |
Index |
Type |
Columns |
AK_SalesTerritoryHistory_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_SalesTerritoryHistory_EndDate |
([EndDate]>=[StartDate] OR [EndDate] IS NULL) |
Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL |
Relation |
Column |
Referenced Column |
Sales.SalesPerson |
SalesPersonID |
SalesPersonID |
Sales.SalesTerritory |
TerritoryID |
TerritoryID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Production.
ScrapReason |
Description |
Manufacturing failure reasons lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
ScrapReasonID |
smallint |
not null |
|
Primary key for ScrapReason records. |
Name |
dbo.Name |
not null |
|
Failure description. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ScrapReason_ScrapReasonID |
ScrapReasonID |
Index |
Type |
Columns |
AK_ScrapReason_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
Production.WorkOrder |
ScrapReasonID |
ScrapReasonID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Table |
HumanResources.
Shift |
Description |
Work shift lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
ShiftID |
tinyint |
not null |
|
Primary key for Shift records. |
Name |
dbo.Name |
not null |
|
Shift description. |
StartTime |
datetime |
not null |
|
Shift start time. |
EndTime |
datetime |
not null |
|
Shift end time. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Shift_ShiftID |
ShiftID |
Index |
Type |
Columns |
AK_Shift_Name |
Unique |
Name |
AK_Shift_StartTime_EndTime |
Unique |
StartTime, EndTime |
Detail Table |
Column |
Referencing Column |
HumanResources.EmployeeDepartmentHistory |
ShiftID |
ShiftID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
HumanResources |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployeeDepartmentHistory |
Table |
Purchasing.
ShipMethod |
Description |
Shipping company lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
ShipMethodID |
int |
not null |
|
Primary key for ShipMethod records. |
Name |
dbo.Name |
not null |
|
Shipping company name. |
ShipBase |
money |
not null |
((0.00))
|
Minimum shipping charge. |
ShipRate |
money |
not null |
((0.00))
|
Shipping charge per pound. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ShipMethod_ShipMethodID |
ShipMethodID |
Index |
Type |
Columns |
AK_ShipMethod_Name |
Unique |
Name |
AK_ShipMethod_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_ShipMethod_ShipBase |
([ShipBase]>(0.00)) |
Check constraint [ShipBase] > (0.00) |
CK_ShipMethod_ShipRate |
([ShipRate]>(0.00)) |
Check constraint [ShipRate] > (0.00) |
Detail Table |
Column |
Referencing Column |
Purchasing.PurchaseOrderHeader |
ShipMethodID |
ShipMethodID |
Sales.SalesOrderHeader |
ShipMethodID |
ShipMethodID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Purchasing |
Table |
Sales.
ShoppingCartItem |
Description |
Contains online customer orders until the order is submitted or cancelled. |
Column |
Data Type |
Nullable |
Default |
Description |
ShoppingCartItemID |
int |
not null |
|
Primary key for ShoppingCartItem records. |
ShoppingCartID |
nvarchar(50) |
not null |
|
Shopping cart identification number. |
Quantity |
int |
not null |
((1))
|
Product quantity ordered. |
ProductID |
int |
not null |
|
Product ordered. Foreign key to Product.ProductID. |
DateCreated |
datetime |
not null |
(getdate())
|
Date the time the record was created. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_ShoppingCartItem_ShoppingCartItemID |
ShoppingCartItemID |
Index |
Type |
Columns |
IX_ShoppingCartItem_ShoppingCartID_ProductID |
|
ShoppingCartID, ProductID |
Check Constraint |
Expression |
Description |
CK_ShoppingCartItem_Quantity |
([Quantity]>=(1)) |
Check constraint [Quantity] >= (1) |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Sales.
SpecialOffer |
Description |
Sale discounts lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
SpecialOfferID |
int |
not null |
|
Primary key for SpecialOffer records. |
Description |
nvarchar(255) |
not null |
|
Discount description. |
DiscountPct |
smallmoney |
not null |
((0.00))
|
Discount precentage. |
Type |
nvarchar(50) |
not null |
|
Discount type category. |
Category |
nvarchar(50) |
not null |
|
Group the discount applies to such as Reseller or Customer. |
StartDate |
datetime |
not null |
|
Discount start date. |
EndDate |
datetime |
not null |
|
Discount end date. |
MinQty |
int |
not null |
((0))
|
Minimum discount percent allowed. |
MaxQty |
int |
null |
|
Maximum discount percent allowed. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SpecialOffer_SpecialOfferID |
SpecialOfferID |
Index |
Type |
Columns |
AK_SpecialOffer_rowguid |
Unique |
rowguid |
Check Constraint |
Expression |
Description |
CK_SpecialOffer_DiscountPct |
([DiscountPct]>=(0.00)) |
Check constraint [DiscountPct] >= (0.00) |
CK_SpecialOffer_EndDate |
([EndDate]>=[StartDate]) |
Check constraint [EndDate] >= [StartDate] |
CK_SpecialOffer_MaxQty |
([MaxQty]>=(0)) |
Check constraint [MaxQty] >= (0) |
CK_SpecialOffer_MinQty |
([MinQty]>=(0)) |
Check constraint [MinQty] >= (0) |
Detail Table |
Column |
Referencing Column |
Sales.SpecialOfferProduct |
SpecialOfferID |
SpecialOfferID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Sales.
SpecialOfferProduct |
Description |
Cross-reference table mapping products to special offer discounts. |
Column |
Data Type |
Nullable |
Default |
Description |
SpecialOfferID |
int |
not null |
|
Primary key for SpecialOfferProduct records. |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_SpecialOfferProduct_SpecialOfferID_ProductID |
SpecialOfferID, ProductID |
Index |
Type |
Columns |
AK_SpecialOfferProduct_rowguid |
Unique |
rowguid |
IX_SpecialOfferProduct_ProductID |
|
ProductID |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Sales.SpecialOffer |
SpecialOfferID |
SpecialOfferID |
Detail Table |
Column |
Referencing Column |
Sales.SalesOrderDetail |
SpecialOfferID |
SpecialOfferID |
Sales.SalesOrderDetail |
SpecialOfferID |
SpecialOfferID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Table |
Person.
StateProvince |
Description |
State and province lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
StateProvinceID |
int |
not null |
|
Primary key for StateProvince records. |
StateProvinceCode |
nchar(3) |
not null |
|
ISO standard state or province code. |
CountryRegionCode |
nvarchar(3) |
not null |
|
ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. |
IsOnlyStateProvinceFlag |
dbo.Flag |
not null |
((1))
|
0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode. |
Name |
dbo.Name |
not null |
|
State or province description. |
TerritoryID |
int |
not null |
|
ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_StateProvince_StateProvinceID |
StateProvinceID |
Index |
Type |
Columns |
AK_StateProvince_Name |
Unique |
Name |
AK_StateProvince_StateProvinceCode_CountryRegionCode |
Unique |
StateProvinceCode, CountryRegionCode |
AK_StateProvince_rowguid |
Unique |
rowguid |
Relation |
Column |
Referenced Column |
Person.CountryRegion |
CountryRegionCode |
CountryRegionCode |
Sales.SalesTerritory |
TerritoryID |
TerritoryID |
Detail Table |
Column |
Referencing Column |
Person.Address |
StateProvinceID |
StateProvinceID |
Sales.SalesTaxRate |
StateProvinceID |
StateProvinceID |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Flag |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Person |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
HumanResources.
vEmployee |
Select |
View |
Person.
vStateProvinceCountryRegion |
Select |
View |
Purchasing.
vVendor |
Select |
View |
Sales.
vIndividualCustomer |
Select |
View |
Sales.
vSalesPerson |
Select |
View |
Sales.
vStoreWithDemographics |
Table |
Sales.
Store |
Description |
Customers (resellers) of Adventure Works products. |
Column |
Data Type |
Nullable |
Default |
Description |
CustomerID |
int |
not null |
|
Primary key. Foreign key to Customer.CustomerID. |
Name |
dbo.Name |
not null |
|
Name of the store. |
SalesPersonID |
int |
null |
|
ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID. |
Demographics |
xml |
null |
|
Demographic informationg about the store such as the number of employees, annual sales and store type. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Store_CustomerID |
CustomerID |
Index |
Type |
Columns |
AK_Store_rowguid |
Unique |
rowguid |
IX_Store_SalesPersonID |
|
SalesPersonID |
PXML_Store_Demographics |
|
Demographics |
Relation |
Column |
Referenced Column |
Sales.Customer |
CustomerID |
CustomerID |
Sales.SalesPerson |
SalesPersonID |
SalesPersonID |
Detail Table |
Column |
Referencing Column |
Sales.StoreContact |
CustomerID |
CustomerID |
Trigger |
Type |
iStore |
ON INSERT |
CREATE TRIGGER [Sales].[iStore] ON [Sales].[Store] AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY -- Only allow the Customer to be a Store OR Individual IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Individual] ON inserted.[CustomerID] = [Sales].[Individual].[CustomerID]) BEGIN -- Rollback any active or uncommittable transactions IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END END; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Data Type |
Type |
dbo.
Name |
|
|
Schema |
Schema |
Sales |
|
|
Data Type |
XML Schema Collection |
Sales.
StoreSurveySchemaCollection |
|
|
Select |
Table |
Sales.
Individual |
Trigger |
iStore |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
iStore |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
iStore |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Select |
View |
Sales.
vStoreWithDemographics |
|
|
Select |
Table |
Sales.
Individual |
Trigger |
iuIndividual |
Table |
Sales.
StoreContact |
Description |
Cross-reference table mapping stores and their employees. |
Column |
Data Type |
Nullable |
Default |
Description |
CustomerID |
int |
not null |
|
Store identification number. Foreign key to Customer.CustomerID. |
ContactID |
int |
not null |
|
Contact (store employee) identification number. Foreign key to Contact.ContactID. |
ContactTypeID |
int |
not null |
|
Contact type such as owner or purchasing agent. Foreign key to ContactType.ContactTypeID. |
rowguid |
uniqueidentifier |
not null |
(newid())
|
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_StoreContact_CustomerID_ContactID |
CustomerID, ContactID |
Index |
Type |
Columns |
AK_StoreContact_rowguid |
Unique |
rowguid |
IX_StoreContact_ContactID |
|
ContactID |
IX_StoreContact_ContactTypeID |
|
ContactTypeID |
Relation |
Column |
Referenced Column |
Person.Contact |
ContactID |
ContactID |
Person.ContactType |
ContactTypeID |
ContactTypeID |
Sales.Store |
CustomerID |
CustomerID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Sales.
vStoreWithDemographics |
Select |
SQL table-valued-function |
dbo.
ufnGetContactInformation |
Table |
Production.
TransactionHistory |
Description |
Record of each purchase order, sales order, or work order transaction year to date. |
Column |
Data Type |
Nullable |
Default |
Description |
TransactionID |
int |
not null |
|
Primary key for TransactionHistory records. |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
ReferenceOrderID |
int |
not null |
|
Purchase order, sales order, or work order identification number. |
ReferenceOrderLineID |
int |
not null |
((0))
|
Line number associated with the purchase order, sales order, or work order. |
TransactionDate |
datetime |
not null |
(getdate())
|
Date and time of the transaction. |
TransactionType |
nchar(1) |
not null |
|
W = WorkOrder, S = SalesOrder, P = PurchaseOrder |
Quantity |
int |
not null |
|
Product quantity. |
ActualCost |
money |
not null |
|
Product cost. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_TransactionHistory_TransactionID |
TransactionID |
Index |
Type |
Columns |
IX_TransactionHistory_ProductID |
|
ProductID |
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID |
|
ReferenceOrderID, ReferenceOrderLineID |
Check Constraint |
Expression |
Description |
CK_TransactionHistory_TransactionType |
(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W') |
Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W') |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Insert |
Table |
Production.
WorkOrder |
Trigger |
iWorkOrder |
Insert |
Table |
Production.
WorkOrder |
Trigger |
uWorkOrder |
Insert |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
iPurchaseOrderDetail |
Insert |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Insert |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Table |
Production.
TransactionHistoryArchive |
Description |
Transactions for previous years. |
Column |
Data Type |
Nullable |
Default |
Description |
TransactionID |
int |
not null |
|
Primary key for TransactionHistoryArchive records. |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
ReferenceOrderID |
int |
not null |
|
Purchase order, sales order, or work order identification number. |
ReferenceOrderLineID |
int |
not null |
((0))
|
Line number associated with the purchase order, sales order, or work order. |
TransactionDate |
datetime |
not null |
(getdate())
|
Date and time of the transaction. |
TransactionType |
nchar(1) |
not null |
|
W = Work Order, S = Sales Order, P = Purchase Order |
Quantity |
int |
not null |
|
Product quantity. |
ActualCost |
money |
not null |
|
Product cost. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_TransactionHistoryArchive_TransactionID |
TransactionID |
Index |
Type |
Columns |
IX_TransactionHistoryArchive_ProductID |
|
ProductID |
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID |
|
ReferenceOrderID, ReferenceOrderLineID |
Check Constraint |
Expression |
Description |
CK_TransactionHistoryArchive_TransactionType |
(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W') |
Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W' |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Table |
Production.
UnitMeasure |
Description |
Unit of measure lookup table. |
Column |
Data Type |
Nullable |
Default |
Description |
UnitMeasureCode |
nchar(3) |
not null |
|
Primary key. |
Name |
dbo.Name |
not null |
|
Unit of measure description. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_UnitMeasure_UnitMeasureCode |
UnitMeasureCode |
Index |
Type |
Columns |
AK_UnitMeasure_Name |
Unique |
Name |
Detail Table |
Column |
Referencing Column |
Production.BillOfMaterials |
UnitMeasureCode |
UnitMeasureCode |
Production.Product |
UnitMeasureCode |
SizeUnitMeasureCode |
Production.Product |
UnitMeasureCode |
WeightUnitMeasureCode |
Purchasing.ProductVendor |
UnitMeasureCode |
UnitMeasureCode |
Dependency Type |
Object Type |
Referenced Object |
Data Type |
Type |
dbo.
Name |
Schema |
Schema |
Production |
Table |
Purchasing.
Vendor |
Description |
Companies from whom Adventure Works Cycles purchases parts or other goods. |
Column |
Data Type |
Nullable |
Default |
Description |
VendorID |
int |
not null |
|
Primary key for Vendor records. |
AccountNumber |
dbo.AccountNumber |
not null |
|
Vendor account (identification) number. |
Name |
dbo.Name |
not null |
|
Company name. |
CreditRating |
tinyint |
not null |
|
1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average |
PreferredVendorStatus |
dbo.Flag |
not null |
((1))
|
0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product. |
ActiveFlag |
dbo.Flag |
not null |
((1))
|
0 = Vendor no longer used. 1 = Vendor is actively used. |
PurchasingWebServiceURL |
nvarchar(1024) |
null |
|
Vendor URL. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_Vendor_VendorID |
VendorID |
Index |
Type |
Columns |
AK_Vendor_AccountNumber |
Unique |
AccountNumber |
Check Constraint |
Expression |
Description |
CK_Vendor_CreditRating |
([CreditRating]>=(1) AND [CreditRating]<=(5)) |
Check constraint [CreditRating] BETWEEN (1) AND (5) |
Detail Table |
Column |
Referencing Column |
Purchasing.ProductVendor |
VendorID |
VendorID |
Purchasing.PurchaseOrderHeader |
VendorID |
VendorID |
Purchasing.VendorAddress |
VendorID |
VendorID |
Purchasing.VendorContact |
VendorID |
VendorID |
Trigger |
Type |
dVendor |
INSTEAD OF DELETE |
CREATE TRIGGER [Purchasing].[dVendor] ON [Purchasing].[Vendor] INSTEAD OF DELETE NOT FOR REPLICATION AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY DECLARE @DeleteCount int; SELECT @DeleteCount = COUNT(*) FROM deleted; IF @DeleteCount > 0 BEGIN RAISERROR (N'Vendors cannot be deleted. They can only be marked as not active.', -- Message 10, -- Severity. 1); -- State. -- Rollback any active or uncommittable transactions IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END END; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Data Type |
Type |
dbo.
AccountNumber |
|
|
Data Type |
Type |
dbo.
Flag |
|
|
Data Type |
Type |
dbo.
Name |
|
|
Schema |
Schema |
Purchasing |
|
|
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
dVendor |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
dVendor |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Purchasing.
vVendor |
Table |
Purchasing.
VendorAddress |
Description |
Cross-reference mapping vendors and addresses. |
Column |
Data Type |
Nullable |
Default |
Description |
VendorID |
int |
not null |
|
Primary key. Foreign key to Vendor.VendorID. |
AddressID |
int |
not null |
|
Primary key. Foreign key to Address.AddressID. |
AddressTypeID |
int |
not null |
|
Address type. Foreign key to AddressType.AddressTypeID. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_VendorAddress_VendorID_AddressID |
VendorID, AddressID |
Index |
Type |
Columns |
IX_VendorAddress_AddressID |
|
AddressID |
Relation |
Column |
Referenced Column |
Person.Address |
AddressID |
AddressID |
Person.AddressType |
AddressTypeID |
AddressTypeID |
Purchasing.Vendor |
VendorID |
VendorID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Purchasing |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Purchasing.
vVendor |
Table |
Purchasing.
VendorContact |
Description |
Cross-reference table mapping vendors and their employees. |
Column |
Data Type |
Nullable |
Default |
Description |
VendorID |
int |
not null |
|
Primary key. |
ContactID |
int |
not null |
|
Contact (Vendor employee) identification number. Foreign key to Contact.ContactID. |
ContactTypeID |
int |
not null |
|
Contact type such as sales manager, or sales agent. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_VendorContact_VendorID_ContactID |
VendorID, ContactID |
Index |
Type |
Columns |
IX_VendorContact_ContactID |
|
ContactID |
IX_VendorContact_ContactTypeID |
|
ContactTypeID |
Relation |
Column |
Referenced Column |
Person.Contact |
ContactID |
ContactID |
Person.ContactType |
ContactTypeID |
ContactTypeID |
Purchasing.Vendor |
VendorID |
VendorID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Purchasing |
Reference Type |
Object Type |
Referencing Object |
Select |
View |
Purchasing.
vVendor |
Select |
SQL table-valued-function |
dbo.
ufnGetContactInformation |
Table |
Production.
WorkOrder |
Description |
Manufacturing work orders. |
Column |
Data Type |
Nullable |
Default |
Description |
WorkOrderID |
int |
not null |
|
Primary key for WorkOrder records. |
ProductID |
int |
not null |
|
Product identification number. Foreign key to Product.ProductID. |
OrderQty |
int |
not null |
|
Product quantity to build. |
StockedQty |
|
|
|
Quantity built and put in inventory. |
ScrappedQty |
smallint |
not null |
|
Quantity that failed inspection. |
StartDate |
datetime |
not null |
|
Work order start date. |
EndDate |
datetime |
null |
|
Work order end date. |
DueDate |
datetime |
not null |
|
Work order due date. |
ScrapReasonID |
smallint |
null |
|
Reason for inspection failure. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_WorkOrder_WorkOrderID |
WorkOrderID |
Index |
Type |
Columns |
IX_WorkOrder_ScrapReasonID |
|
ScrapReasonID |
IX_WorkOrder_ProductID |
|
ProductID |
Check Constraint |
Expression |
Description |
CK_WorkOrder_EndDate |
([EndDate]>=[StartDate] OR [EndDate] IS NULL) |
Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NULL |
CK_WorkOrder_OrderQty |
([OrderQty]>(0)) |
Check constraint [OrderQty] > (0) |
CK_WorkOrder_ScrappedQty |
([ScrappedQty]>=(0)) |
Check constraint [ScrappedQty] >= (0) |
Relation |
Column |
Referenced Column |
Production.Product |
ProductID |
ProductID |
Production.ScrapReason |
ScrapReasonID |
ScrapReasonID |
Detail Table |
Column |
Referencing Column |
Production.WorkOrderRouting |
WorkOrderID |
WorkOrderID |
Trigger |
Type |
iWorkOrder |
ON INSERT |
uWorkOrder |
ON UPDATE |
CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder] AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY INSERT INTO [Production].[TransactionHistory]( [ProductID] ,[ReferenceOrderID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[WorkOrderID] ,'W' ,GETDATE() ,inserted.[OrderQty] ,0 FROM inserted; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY IF UPDATE([ProductID]) OR UPDATE([OrderQty]) BEGIN INSERT INTO [Production].[TransactionHistory]( [ProductID] ,[ReferenceOrderID] ,[TransactionType] ,[TransactionDate] ,[Quantity]) SELECT inserted.[ProductID] ,inserted.[WorkOrderID] ,'W' ,GETDATE() ,inserted.[OrderQty] FROM inserted; END; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- 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;
Dependency Type |
Object Type |
Referenced Object |
Child Type |
Child Object |
Schema |
Schema |
Production |
|
|
Insert |
Table |
Production.
TransactionHistory |
Trigger |
iWorkOrder |
Insert |
Table |
Production.
TransactionHistory |
Trigger |
uWorkOrder |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
iWorkOrder |
Execute |
Procedure |
dbo.
uspLogError |
Trigger |
uWorkOrder |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
uWorkOrder |
Execute |
Procedure |
dbo.
uspPrintError |
Trigger |
iWorkOrder |
Table |
Production.
WorkOrderRouting |
Description |
Work order details. |
Column |
Data Type |
Nullable |
Default |
Description |
WorkOrderID |
int |
not null |
|
Primary key. Foreign key to WorkOrder.WorkOrderID. |
ProductID |
int |
not null |
|
Primary key. Foreign key to Product.ProductID. |
OperationSequence |
smallint |
not null |
|
Primary key. Indicates the manufacturing process sequence. |
LocationID |
smallint |
not null |
|
Manufacturing location where the part is processed. Foreign key to Location.LocationID. |
ScheduledStartDate |
datetime |
not null |
|
Planned manufacturing start date. |
ScheduledEndDate |
datetime |
not null |
|
Planned manufacturing end date. |
ActualStartDate |
datetime |
null |
|
Actual start date. |
ActualEndDate |
datetime |
null |
|
Actual end date. |
ActualResourceHrs |
decimal(9, 4) |
null |
|
Number of manufacturing hours used. |
PlannedCost |
money |
not null |
|
Estimated manufacturing cost. |
ActualCost |
money |
null |
|
Actual manufacturing cost. |
ModifiedDate |
datetime |
not null |
(getdate())
|
Date and time the record was last updated. |
Primary Key |
Columns |
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence |
WorkOrderID, ProductID, OperationSequence |
Index |
Type |
Columns |
IX_WorkOrderRouting_ProductID |
|
ProductID |
Check Constraint |
Expression |
Description |
CK_WorkOrderRouting_ActualCost |
([ActualCost]>(0.00)) |
Check constraint [ActualCost] > (0.00) |
CK_WorkOrderRouting_ActualEndDate |
([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL) |
Check constraint [ActualEndDate] >= [ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL |
CK_WorkOrderRouting_ActualResourceHrs |
([ActualResourceHrs]>=(0.0000)) |
Check constraint [ActualResourceHrs] >= (0.0000) |
CK_WorkOrderRouting_PlannedCost |
([PlannedCost]>(0.00)) |
Check constraint [PlannedCost] > (0.00) |
CK_WorkOrderRouting_ScheduledEndDate |
([ScheduledEndDate]>=[ScheduledStartDate]) |
Check constraint [ScheduledEndDate] >= [ScheduledStartDate] |
Relation |
Column |
Referenced Column |
Production.Location |
LocationID |
LocationID |
Production.WorkOrder |
WorkOrderID |
WorkOrderID |
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
View |
Person.
vAdditionalContactInfo |
Description |
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that 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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Person.
Contact |
Schema |
Schema |
Person |
View |
HumanResources.
vEmployee |
Description |
Employee names and addresses. |
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];
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
HumanResources.
EmployeeAddress |
Select |
Table |
Person.
Address |
Select |
Table |
Person.
Contact |
Select |
Table |
Person.
CountryRegion |
Select |
Table |
Person.
StateProvince |
Schema |
Schema |
HumanResources |
View |
HumanResources.
vEmployeeDepartment |
Description |
Returns employee name, title, and current department. |
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());
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Department |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
HumanResources.
EmployeeDepartmentHistory |
Select |
Table |
Person.
Contact |
Schema |
Schema |
HumanResources |
View |
HumanResources.
vEmployeeDepartmentHistory |
Description |
Returns employee name and current and previous departments. |
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];
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Department |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
HumanResources.
EmployeeDepartmentHistory |
Select |
Table |
HumanResources.
Shift |
Select |
Table |
Person.
Contact |
Schema |
Schema |
HumanResources |
View |
Sales.
vIndividualCustomer |
Description |
Individual customers (names and addresses) that purchase Adventure Works Cycles products online. |
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');
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Person.
Address |
Select |
Table |
Person.
AddressType |
Select |
Table |
Person.
Contact |
Select |
Table |
Person.
CountryRegion |
Select |
Table |
Person.
StateProvince |
Select |
Table |
Sales.
Customer |
Select |
Table |
Sales.
CustomerAddress |
Select |
Table |
Sales.
Individual |
Schema |
Schema |
Sales |
View |
Sales.
vIndividualDemographics |
Description |
Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Sales.
Individual |
Schema |
Schema |
Sales |
View |
HumanResources.
vJobCandidate |
Description |
Job candidate names and resumes. |
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);
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
JobCandidate |
Schema |
Schema |
HumanResources |
View |
HumanResources.
vJobCandidateEducation |
Description |
Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed. |
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);
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
JobCandidate |
Schema |
Schema |
HumanResources |
View |
HumanResources.
vJobCandidateEmployment |
Description |
Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed. |
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);
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
JobCandidate |
Schema |
Schema |
HumanResources |
View |
Production.
vProductAndDescription |
Description |
Product names and descriptions. Product descriptions are provided in multiple languages. |
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];
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
Product |
Select |
Table |
Production.
ProductDescription |
Select |
Table |
Production.
ProductModel |
Select |
Table |
Production.
ProductModelProductDescriptionCulture |
Schema |
Schema |
Production |
View |
Production.
vProductModelCatalogDescription |
Description |
Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
ProductModel |
Schema |
Schema |
Production |
View |
Production.
vProductModelInstructions |
Description |
Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions. |
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);
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
ProductModel |
Schema |
Schema |
Production |
View |
Sales.
vSalesPerson |
Description |
Sales representiatives (names and addresses) and their sales-related information. |
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];
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
HumanResources.
EmployeeAddress |
Select |
Table |
Person.
Address |
Select |
Table |
Person.
Contact |
Select |
Table |
Person.
CountryRegion |
Select |
Table |
Person.
StateProvince |
Select |
Table |
Sales.
SalesPerson |
Select |
Table |
Sales.
SalesTerritory |
Schema |
Schema |
Sales |
View |
Sales.
vSalesPersonSalesByFiscalYears |
Description |
Uses PIVOT to return aggregated sales information for each sales representative. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
Person.
Contact |
Select |
Table |
Sales.
SalesOrderHeader |
Select |
Table |
Sales.
SalesPerson |
Select |
Table |
Sales.
SalesTerritory |
Schema |
Schema |
Sales |
View |
Person.
vStateProvinceCountryRegion |
Description |
Joins StateProvince table with CountryRegion table. |
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];
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Person.
CountryRegion |
Select |
Table |
Person.
StateProvince |
Schema |
Schema |
Person |
View |
Sales.
vStoreWithDemographics |
Description |
Stores (names and addresses) that sell Adventure Works Cycles products to consumers. |
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');
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Person.
Address |
Select |
Table |
Person.
AddressType |
Select |
Table |
Person.
Contact |
Select |
Table |
Person.
ContactType |
Select |
Table |
Person.
CountryRegion |
Select |
Table |
Person.
StateProvince |
Select |
Table |
Sales.
Customer |
Select |
Table |
Sales.
CustomerAddress |
Select |
Table |
Sales.
Store |
Select |
Table |
Sales.
StoreContact |
Schema |
Schema |
Sales |
View |
Purchasing.
vVendor |
Description |
Vendor (company) names and addresses and the names of vendor employees to contact. |
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];
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Person.
Address |
Select |
Table |
Person.
Contact |
Select |
Table |
Person.
ContactType |
Select |
Table |
Person.
CountryRegion |
Select |
Table |
Person.
StateProvince |
Select |
Table |
Purchasing.
Vendor |
Select |
Table |
Purchasing.
VendorAddress |
Select |
Table |
Purchasing.
VendorContact |
Schema |
Schema |
Purchasing |
Procedure |
dbo.
uspGetBillOfMaterials |
Description |
Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
BillOfMaterials |
Select |
Table |
Production.
Product |
Procedure |
dbo.
uspGetEmployeeManagers |
Description |
Stored procedure using a recursive query to return the direct and indirect managers of the specified employee. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
Person.
Contact |
Procedure |
dbo.
uspGetManagerEmployees |
Description |
Stored procedure using a recursive query to return the direct and indirect employees of the specified manager. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
Person.
Contact |
Procedure |
dbo.
uspGetWhereUsedProductID |
Description |
Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
BillOfMaterials |
Select |
Table |
Production.
Product |
Procedure |
dbo.
uspLogError |
Description |
Logs error information in the ErrorLog table 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 inserting error information. |
-- 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;
Dependency Type |
Object Type |
Referenced Object |
Insert |
Table |
dbo.
ErrorLog |
Execute |
Procedure |
dbo.
uspPrintError |
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Execute |
Procedure |
HumanResources.
uspUpdateEmployeeHireInfo |
|
|
Execute |
Procedure |
HumanResources.
uspUpdateEmployeeLogin |
|
|
Execute |
Procedure |
HumanResources.
uspUpdateEmployeePersonalInfo |
|
|
Execute |
Table |
Production.
WorkOrder |
Trigger |
iWorkOrder |
Execute |
Table |
Production.
WorkOrder |
Trigger |
uWorkOrder |
Execute |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
iPurchaseOrderDetail |
Execute |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Execute |
Table |
Purchasing.
PurchaseOrderHeader |
Trigger |
uPurchaseOrderHeader |
Execute |
Table |
Purchasing.
Vendor |
Trigger |
dVendor |
Execute |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Execute |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Execute |
Table |
Sales.
Store |
Trigger |
iStore |
Procedure |
dbo.
uspPrintError |
Description |
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. |
-- 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;
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Execute |
Procedure |
dbo.
uspLogError |
|
|
Execute |
Table |
Production.
WorkOrder |
Trigger |
iWorkOrder |
Execute |
Table |
Production.
WorkOrder |
Trigger |
uWorkOrder |
Execute |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
iPurchaseOrderDetail |
Execute |
Table |
Purchasing.
PurchaseOrderDetail |
Trigger |
uPurchaseOrderDetail |
Execute |
Table |
Purchasing.
PurchaseOrderHeader |
Trigger |
uPurchaseOrderHeader |
Execute |
Table |
Purchasing.
Vendor |
Trigger |
dVendor |
Execute |
Table |
Sales.
SalesOrderDetail |
Trigger |
iduSalesOrderDetail |
Execute |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Execute |
Table |
Sales.
Store |
Trigger |
iStore |
Procedure |
HumanResources.
uspUpdateEmployeeHireInfo |
Description |
Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters. |
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;
Dependency Type |
Object Type |
Referenced Object |
Update |
Table |
HumanResources.
Employee |
Insert |
Table |
HumanResources.
EmployeePayHistory |
Execute |
Procedure |
dbo.
uspLogError |
Schema |
Schema |
HumanResources |
Procedure |
HumanResources.
uspUpdateEmployeeLogin |
Description |
Updates the Employee table with the values specified in the input parameters for the given EmployeeID. |
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;
Dependency Type |
Object Type |
Referenced Object |
Update |
Table |
HumanResources.
Employee |
Execute |
Procedure |
dbo.
uspLogError |
Schema |
Schema |
HumanResources |
Procedure |
HumanResources.
uspUpdateEmployeePersonalInfo |
Description |
Updates the Employee table with the values specified in the input parameters for the given EmployeeID. |
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;
Dependency Type |
Object Type |
Referenced Object |
Update |
Table |
HumanResources.
Employee |
Execute |
Procedure |
dbo.
uspLogError |
Schema |
Schema |
HumanResources |
Function |
dbo.
ufnGetAccountingEndDate |
Description |
Scalar function used in the uSalesOrderHeader trigger to set the starting account date. |
CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]() RETURNS [datetime] AS BEGIN RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112)); END;
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Execute |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Function |
dbo.
ufnGetAccountingStartDate |
Description |
Scalar function used in the uSalesOrderHeader trigger to set the ending account date. |
CREATE FUNCTION [dbo].[ufnGetAccountingStartDate]() RETURNS [datetime] AS BEGIN RETURN CONVERT(datetime, '20030701', 112); END;
Reference Type |
Object Type |
Referencing Object |
Child Type |
Child Object |
Execute |
Table |
Sales.
SalesOrderHeader |
Trigger |
uSalesOrderHeader |
Function |
dbo.
ufnGetDocumentStatusText |
Description |
Scalar function returning the text representation of the Status column in the Document table. |
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;
Function |
dbo.
ufnGetProductDealerPrice |
Description |
Scalar function returning the dealer price for a given product on a particular order date. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
Product |
Select |
Table |
Production.
ProductListPriceHistory |
Function |
dbo.
ufnGetProductListPrice |
Description |
Scalar function returning the list price for a given product on a particular order date. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
Product |
Select |
Table |
Production.
ProductListPriceHistory |
Function |
dbo.
ufnGetProductStandardCost |
Description |
Scalar function returning the standard cost for a given product on a particular order date. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
Product |
Select |
Table |
Production.
ProductCostHistory |
Function |
dbo.
ufnGetPurchaseOrderStatusText |
Description |
Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table. |
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;
Function |
dbo.
ufnGetSalesOrderStatusText |
Description |
Scalar function returning the text representation of the Status column in the SalesOrderHeader table. |
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;
Function |
dbo.
ufnGetStock |
Description |
Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
Production.
ProductInventory |
Function |
dbo.
ufnLeadingZeros |
Description |
Scalar function used by the Sales.Customer table to help set the account number. |
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;
Function |
dbo.
ufnGetContactInformation |
Description |
Table value function returning the first name, last name, job title and contact type for a given contact. |
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;
Dependency Type |
Object Type |
Referenced Object |
Select |
Table |
HumanResources.
Employee |
Select |
Table |
Person.
Contact |
Select |
Table |
Person.
ContactType |
Select |
Table |
Purchasing.
VendorContact |
Select |
Table |
Sales.
Individual |
Select |
Table |
Sales.
StoreContact |
Schema |
HumanResources |
Description |
Contains objects related to employees and departments. |
CREATE SCHEMA HumanResources AUTHORIZATION dbo
Reference Type |
Object Type |
Referencing Object |
Schema |
Table |
HumanResources.
Department |
Schema |
Table |
HumanResources.
Employee |
Schema |
Table |
HumanResources.
EmployeeAddress |
Schema |
Table |
HumanResources.
EmployeeDepartmentHistory |
Schema |
Table |
HumanResources.
EmployeePayHistory |
Schema |
Table |
HumanResources.
JobCandidate |
Schema |
Table |
HumanResources.
Shift |
Schema |
View |
HumanResources.
vEmployee |
Schema |
View |
HumanResources.
vEmployeeDepartment |
Schema |
View |
HumanResources.
vEmployeeDepartmentHistory |
Schema |
View |
HumanResources.
vJobCandidate |
Schema |
View |
HumanResources.
vJobCandidateEducation |
Schema |
View |
HumanResources.
vJobCandidateEmployment |
Schema |
Procedure |
HumanResources.
uspUpdateEmployeeHireInfo |
Schema |
Procedure |
HumanResources.
uspUpdateEmployeeLogin |
Schema |
Procedure |
HumanResources.
uspUpdateEmployeePersonalInfo |
Schema |
XML Schema Collection |
HumanResources.
HRResumeSchemaCollection |
Schema |
Person |
Description |
Contains objects related to names and addresses of customers, vendors, and employees |
CREATE SCHEMA Person AUTHORIZATION dbo
Reference Type |
Object Type |
Referencing Object |
Schema |
Table |
Person.
Address |
Schema |
Table |
Person.
AddressType |
Schema |
Table |
Person.
Contact |
Schema |
Table |
Person.
ContactType |
Schema |
Table |
Person.
CountryRegion |
Schema |
Table |
Person.
StateProvince |
Schema |
View |
Person.
vAdditionalContactInfo |
Schema |
View |
Person.
vStateProvinceCountryRegion |
Schema |
XML Schema Collection |
Person.
AdditionalContactInfoSchemaCollection |
Schema |
Production |
Description |
Contains objects related to products, inventory, and manufacturing. |
CREATE SCHEMA Production AUTHORIZATION dbo
Reference Type |
Object Type |
Referencing Object |
Schema |
Table |
Production.
BillOfMaterials |
Schema |
Table |
Production.
Culture |
Schema |
Table |
Production.
Document |
Schema |
Table |
Production.
Illustration |
Schema |
Table |
Production.
Location |
Schema |
Table |
Production.
Product |
Schema |
Table |
Production.
ProductCategory |
Schema |
Table |
Production.
ProductCostHistory |
Schema |
Table |
Production.
ProductDescription |
Schema |
Table |
Production.
ProductDocument |
Schema |
Table |
Production.
ProductInventory |
Schema |
Table |
Production.
ProductListPriceHistory |
Schema |
Table |
Production.
ProductModel |
Schema |
Table |
Production.
ProductModelIllustration |
Schema |
Table |
Production.
ProductModelProductDescriptionCulture |
Schema |
Table |
Production.
ProductPhoto |
Schema |
Table |
Production.
ProductProductPhoto |
Schema |
Table |
Production.
ProductReview |
Schema |
Table |
Production.
ProductSubcategory |
Schema |
Table |
Production.
ScrapReason |
Schema |
Table |
Production.
TransactionHistory |
Schema |
Table |
Production.
TransactionHistoryArchive |
Schema |
Table |
Production.
UnitMeasure |
Schema |
Table |
Production.
WorkOrder |
Schema |
Table |
Production.
WorkOrderRouting |
Schema |
View |
Production.
vProductAndDescription |
Schema |
View |
Production.
vProductModelCatalogDescription |
Schema |
View |
Production.
vProductModelInstructions |
Schema |
XML Schema Collection |
Production.
ManuInstructionsSchemaCollection |
Schema |
XML Schema Collection |
Production.
ProductDescriptionSchemaCollection |
Schema |
Purchasing |
Description |
Contains objects related to vendors and purchase orders. |
CREATE SCHEMA Purchasing AUTHORIZATION dbo
Reference Type |
Object Type |
Referencing Object |
Schema |
Table |
Purchasing.
ProductVendor |
Schema |
Table |
Purchasing.
PurchaseOrderDetail |
Schema |
Table |
Purchasing.
PurchaseOrderHeader |
Schema |
Table |
Purchasing.
ShipMethod |
Schema |
Table |
Purchasing.
Vendor |
Schema |
Table |
Purchasing.
VendorAddress |
Schema |
Table |
Purchasing.
VendorContact |
Schema |
View |
Purchasing.
vVendor |
Schema |
Sales |
Description |
Contains objects related to customers, sales orders, and sales territories. |
CREATE SCHEMA Sales AUTHORIZATION dbo
Reference Type |
Object Type |
Referencing Object |
Schema |
Table |
Sales.
ContactCreditCard |
Schema |
Table |
Sales.
CountryRegionCurrency |
Schema |
Table |
Sales.
CreditCard |
Schema |
Table |
Sales.
Currency |
Schema |
Table |
Sales.
CurrencyRate |
Schema |
Table |
Sales.
Customer |
Schema |
Table |
Sales.
CustomerAddress |
Schema |
Table |
Sales.
Individual |
Schema |
Table |
Sales.
SalesOrderDetail |
Schema |
Table |
Sales.
SalesOrderHeader |
Schema |
Table |
Sales.
SalesOrderHeaderSalesReason |
Schema |
Table |
Sales.
SalesPerson |
Schema |
Table |
Sales.
SalesPersonQuotaHistory |
Schema |
Table |
Sales.
SalesReason |
Schema |
Table |
Sales.
SalesTaxRate |
Schema |
Table |
Sales.
SalesTerritory |
Schema |
Table |
Sales.
SalesTerritoryHistory |
Schema |
Table |
Sales.
ShoppingCartItem |
Schema |
Table |
Sales.
SpecialOffer |
Schema |
Table |
Sales.
SpecialOfferProduct |
Schema |
Table |
Sales.
Store |
Schema |
Table |
Sales.
StoreContact |
Schema |
View |
Sales.
vIndividualCustomer |
Schema |
View |
Sales.
vIndividualDemographics |
Schema |
View |
Sales.
vSalesPerson |
Schema |
View |
Sales.
vSalesPersonSalesByFiscalYears |
Schema |
View |
Sales.
vStoreWithDemographics |
Schema |
XML Schema Collection |
Sales.
IndividualSurveySchemaCollection |
Schema |
XML Schema Collection |
Sales.
StoreSurveySchemaCollection |
Type |
dbo.
AccountNumber |
CREATE TYPE dbo.AccountNumber FROM nvarchar(30) NULL
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Purchasing.
Vendor |
Data Type |
Table |
Sales.
SalesOrderHeader |
Type |
dbo.
Flag |
CREATE TYPE dbo.Flag FROM bit NOT NULL
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
HumanResources.
Employee |
Data Type |
Table |
Person.
StateProvince |
Data Type |
Table |
Production.
Product |
Data Type |
Table |
Production.
ProductProductPhoto |
Data Type |
Table |
Purchasing.
Vendor |
Data Type |
Table |
Sales.
SalesOrderHeader |
Type |
dbo.
Name |
CREATE TYPE dbo.[Name] FROM nvarchar(100) NULL
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
HumanResources.
Department |
Data Type |
Table |
HumanResources.
Shift |
Data Type |
Table |
Person.
AddressType |
Data Type |
Table |
Person.
Contact |
Data Type |
Table |
Person.
ContactType |
Data Type |
Table |
Person.
CountryRegion |
Data Type |
Table |
Person.
StateProvince |
Data Type |
Table |
Production.
Culture |
Data Type |
Table |
Production.
Location |
Data Type |
Table |
Production.
Product |
Data Type |
Table |
Production.
ProductCategory |
Data Type |
Table |
Production.
ProductModel |
Data Type |
Table |
Production.
ProductReview |
Data Type |
Table |
Production.
ProductSubcategory |
Data Type |
Table |
Production.
ScrapReason |
Data Type |
Table |
Production.
UnitMeasure |
Data Type |
Table |
Purchasing.
ShipMethod |
Data Type |
Table |
Purchasing.
Vendor |
Data Type |
Table |
Sales.
Currency |
Data Type |
Table |
Sales.
SalesReason |
Data Type |
Table |
Sales.
SalesTaxRate |
Data Type |
Table |
Sales.
SalesTerritory |
Data Type |
Table |
Sales.
Store |
Type |
dbo.
NameStyle |
CREATE TYPE dbo.NameStyle FROM bit NOT NULL
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Person.
Contact |
Type |
dbo.
OrderNumber |
CREATE TYPE dbo.OrderNumber FROM nvarchar(50) NULL
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Sales.
SalesOrderHeader |
Type |
dbo.
Phone |
CREATE TYPE dbo.Phone FROM nvarchar(50) NULL
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Person.
Contact |
XML Schema Collection |
Person.
AdditionalContactInfoSchemaCollection |
Description |
Collection of XML schemas for the AdditionalContactInfo column in the Person.Contact table. |
<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>
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Person |
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Person.
Contact |
XML Schema Collection |
HumanResources.
HRResumeSchemaCollection |
Description |
Collection of XML schemas for the Resume column in the HumanResources.JobCandidate table. |
<?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>
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
HumanResources |
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
HumanResources.
JobCandidate |
XML Schema Collection |
Sales.
IndividualSurveySchemaCollection |
Description |
Collection of XML schemas for the Demographics column in the Sales.Individual table. |
<?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>
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Sales.
Individual |
XML Schema Collection |
Production.
ManuInstructionsSchemaCollection |
Description |
Collection of XML schemas for the Instructions column in the Production.ProductModel table. |
<?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>
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Production.
ProductModel |
XML Schema Collection |
Production.
ProductDescriptionSchemaCollection |
Description |
Collection of XML schemas for the CatalogDescription column in the Production.ProductModel table. |
<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>
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Production |
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Production.
ProductModel |
XML Schema Collection |
Sales.
StoreSurveySchemaCollection |
Description |
Collection of XML schemas for the Demographics column in the Sales.Store table. |
<?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>
Dependency Type |
Object Type |
Referenced Object |
Schema |
Schema |
Sales |
Reference Type |
Object Type |
Referencing Object |
Data Type |
Table |
Sales.
Store |
Database Trigger |
ddlDatabaseTriggerLog |
Description |
Database trigger to audit all of the DDL changes made to the AdventureWorks database. |
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS BEGIN SET NOCOUNT ON; DECLARE @data XML; DECLARE @schema sysname; DECLARE @object sysname; DECLARE @eventType sysname; SET @data = EVENTDATA(); SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'); SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'); SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') IF @object IS NOT NULL PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object; ELSE PRINT ' ' + @eventType + ' - ' + @schema; IF @eventType IS NULL PRINT CONVERT(nvarchar(max), @data); INSERT [dbo].[DatabaseLog] ( [PostTime], [DatabaseUser], [Event], [Schema], [Object], [TSQL], [XmlEvent] ) VALUES ( GETDATE(), CONVERT(sysname, CURRENT_USER), @eventType, CONVERT(sysname, @schema), CONVERT(sysname, @object), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), @data ); END;
Dependency Type |
Object Type |
Referenced Object |
Insert |
Table |
dbo.
DatabaseLog |