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. 


Name Data Type Length NULL Default IsIdentity IsGUID Description
    StateProvinceID  int          Primary key for StateProvince records. 
    StateProvinceCode  nchar          ISO standard state or province code. 
    CountryRegionCode  nvarchar          ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.  
    IsOnlyStateProvinceFlag  Flag    ((1))      0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode. 
    Name  Name  100          State or province description. 
    TerritoryID  int          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    (getdate())      Date and time the record was last updated. 
Total: 8 column(s)

Identity column

Name Seed Increment Not for replication


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 
  Name  dbo  User Defined type 
  CountryRegion  Person  Table 
  SalesTerritory  Sales  Table 
Total: 4 object(s)

Objects that depend on [Person].[StateProvince]

Object Name Owner Object Type Dep Level
  vStateProvinceCountryRegion  Person  View 
  Address  Person  Table 
  SalesTaxRate  Sales  Table 
  CustomerAddress  Sales  Table 
  EmployeeAddress  HumanResources  Table 
  SalesOrderHeader  Sales  Table 
  VendorAddress  Purchasing  Table 
  vEmployee  HumanResources  View 
  vIndividualCustomer  Sales  View 
  vSalesPerson  Sales  View 
  vSalesPersonSalesByFiscalYears  Sales  View 
  vStoreWithDemographics  Sales  View 
  vVendor  Purchasing  View 
  SalesOrderDetail  Sales  Table 
  SalesOrderHeaderSalesReason  Sales  Table 
  uSalesOrderHeader  Sales  Trigger 
  iduSalesOrderDetail  Sales  Trigger 
Total: 17 object(s)


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()),
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_StateProvince_CountryRegion_CountryRegionCode] FOREIGN KEY
    ) REFERENCES [CountryRegion] (
    CONSTRAINT [FK_StateProvince_SalesTerritory_TerritoryID] FOREIGN KEY
    ) REFERENCES [SalesTerritory] (

See Also

List of tables