Properties

Owner:	Person
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	16
Index Size KB:	64
Rows:	181
Description:	State and province lookup table.

Columns

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

Total: 8 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	StateProvinceID	1	1

Indexes

	Index	Primary	Unique	Description
	PK_StateProvince_StateProvinceID			Primary key (clustered) constraint
	AK_StateProvince_Name			Unique nonclustered index.
	AK_StateProvince_StateProvinceCode_CountryRegionCode			Unique nonclustered index.
	AK_StateProvince_rowguid			Unique nonclustered index. Used to support replication samples.

Total: 4 index(es)

Referencing Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Sales.SalesTaxRate	FK_SalesTaxRate_StateProvince_StateProvinceID	PK_StateProvince_StateProvinceID
	Person.Address	FK_Address_StateProvince_StateProvinceID	PK_StateProvince_StateProvinceID

Total: 2 table(s)

Referenced Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Person.CountryRegion	FK_StateProvince_CountryRegion_CountryRegionCode	PK_CountryRegion_CountryRegionCode
	Sales.SalesTerritory	FK_StateProvince_SalesTerritory_TerritoryID	PK_SalesTerritory_TerritoryID

Total: 2 table(s)

Objects that [Person].[StateProvince] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	CountryRegion	Person	Table	2
	SalesTerritory	Sales	Table	2

Total: 4 object(s)

Objects that depend on [Person].[StateProvince]

	Object Name	Owner	Object Type	Dep Level
	vStateProvinceCountryRegion	Person	View	1
	Address	Person	Table	1
	SalesTaxRate	Sales	Table	1
	CustomerAddress	Sales	Table	2
	EmployeeAddress	HumanResources	Table	2
	SalesOrderHeader	Sales	Table	2
	VendorAddress	Purchasing	Table	2
	vEmployee	HumanResources	View	3
	vIndividualCustomer	Sales	View	3
	vSalesPerson	Sales	View	3
	vSalesPersonSalesByFiscalYears	Sales	View	3
	vStoreWithDemographics	Sales	View	3
	vVendor	Purchasing	View	3
	SalesOrderDetail	Sales	Table	3
	SalesOrderHeaderSalesReason	Sales	Table	3
	uSalesOrderHeader	Sales	Trigger	3
	iduSalesOrderDetail	Sales	Trigger	4

Total: 17 object(s)

SQL

CREATE TABLE [StateProvince] (
    [StateProvinceID] [int] IDENTITY (1, 1) NOT NULL ,
    [StateProvinceCode] [nchar] (3) COLLATE Latin1_General_CS_AS NOT NULL ,
    [CountryRegionCode] [nvarchar] (3) COLLATE Latin1_General_CS_AS NOT NULL ,
    [IsOnlyStateProvinceFlag] [Flag] NOT NULL CONSTRAINT [DF_StateProvince_IsOnlyStateProvinceFlag] DEFAULT ((1)),
    [Name] [Name] NOT NULL ,
    [TerritoryID] [int] NOT NULL ,
    [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_StateProvince_rowguid] DEFAULT (newid()),
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_StateProvince_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_StateProvince_StateProvinceID] PRIMARY KEY  CLUSTERED
    (
        [StateProvinceID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_StateProvince_CountryRegion_CountryRegionCode] FOREIGN KEY
    (
        [CountryRegionCode]
    ) REFERENCES [CountryRegion] (
        [CountryRegionCode]
    ),
    CONSTRAINT [FK_StateProvince_SalesTerritory_TerritoryID] FOREIGN KEY
    (
        [TerritoryID]
    ) REFERENCES [SalesTerritory] (
        [TerritoryID]
    )
) ON [PRIMARY]
GO

See Also

List of tables