Purchasing.vVendor View

Description

Vendor (company) names and addresses and the names of vendor employees to contact.

Properties

Creation Date	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
VendorID	int	4
Name	dbo.Name	50
ContactType	dbo.Name	50
Title	nvarchar	8
FirstName	dbo.Name	50
MiddleName	dbo.Name	50
LastName	dbo.Name	50
Suffix	nvarchar	10
Phone	dbo.Phone	25
EmailAddress	nvarchar	50
EmailPromotion	int	4
AddressLine1	nvarchar	60
AddressLine2	nvarchar	60
City	nvarchar	30
StateProvinceName	dbo.Name	50
PostalCode	nvarchar	15
CountryRegionName	dbo.Name	50

Objects that Purchasing.vVendor depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Person.AddressType	Table	Types of addresses stored in the Address table.	2
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
Person.ContactType	Table	Lookup table containing the types of contacts stored in Contact.	1
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	1
dbo.ErrorLog	Table	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.	3
dbo.Flag	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2
Sales.SalesTerritory	Table	Sales territory lookup table.	2
Person.StateProvince	Table	State and province lookup table.	1
dbo.uspLogError	Stored Procedure	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.	2
dbo.uspPrintError	Stored Procedure	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.	2
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	1
Purchasing.VendorAddress	Table	Cross-reference mapping vendors and addresses.	1
Purchasing.VendorContact	Table	Cross-reference table mapping vendors and their employees.	1

View Definition

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];

Related Objects