if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GROSS_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GROSS_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KGS_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KGS_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ORG_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ORG_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ORT_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ORT_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OTL_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OTL_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PLZ_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PLZ_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POFA_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[POFA_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PRUEF_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PRUEF_DA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[STR_DA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[STR_DA]
GO

CREATE TABLE [dbo].[GROSS_DA] (
	[Version_Dateikennung] [char] (2) NOT NULL ,
	[Version_Datum] [char] (4) NOT NULL ,
	[Version_Nr] [char] (3) NOT NULL ,
	[Datum] [char] (10) NOT NULL ,
	[ID_NR] [char] (8) NOT NULL ,
	[Name_Sort] [char] (40) NOT NULL ,
	[Name] [varchar] (160) NOT NULL ,
	[Str_Name_46] [varchar] (50) NULL ,
	[HNR_Von] [char] (8) NULL ,
	[HNR_Bis] [char] (8) NULL ,
	[PLZ_Haus] [char] (5) NULL ,
	[Alort_Haus] [char] (8) NULL ,
	[Fikt_Pfnr] [char] (6) NULL ,
	[PLZ_GE] [char] (5) NOT NULL ,
	[GRGE_CODE] [char] (3) NULL ,
	[PLZ_ART] [char] (1) NOT NULL ,
	[ALORT_GE] [char] (8) NOT NULL 
) 
GO

CREATE TABLE [dbo].[KGS_DA] (
	[versionkennung] [char] (2) NOT NULL ,
	[versiondatum] [char] (4) NOT NULL ,
	[versionnr] [char] (3) NOT NULL ,
	[datum] [char] (8) NOT NULL ,
	[schluessel] [char] (8) NOT NULL ,
	[satzart] [char] (1) NOT NULL ,
	[name] [varchar] (40) NOT NULL 
) 
GO

CREATE TABLE [dbo].[ORG_DA] (
	[Version_Dateikennung] [char] (2) NOT NULL ,
	[Version_Datum] [char] (4) NOT NULL ,
	[Version_Nr] [char] (3) NOT NULL ,
	[Datum] [char] (8) NOT NULL ,
	[ALOrg] [char] (8) NOT NULL ,
	[Name] [varchar] (90) NOT NULL ,
	[NPANST] [char] (3) NULL ,
	[DIRNL_BRIEF] [char] (4) NOT NULL ,
	[PANSTART] [char] (2) NOT NULL ,
	[STR_NAME46] [varchar] (46) NOT NULL ,
	[HNR_VON] [char] (8) NOT NULL ,
	[HNR_BIS] [char] (8) NOT NULL ,
	[PLZ_HAUS] [char] (5) NOT NULL ,
	[PPF_NR_REAL] [char] (6) NULL ,
	[PLZ_PF] [char] (5) NULL ,
	[RESERVE] [char] (6) NULL ,
	[PLZ_GE] [char] (5) NULL ,
	[TELEFON] [varchar] (20) NOT NULL ,
	[TELEFAX] [varchar] (20) NULL ,
	[ALORT_HAUS] [char] (8) NOT NULL ,
	[PLZ_LAGER_B] [char] (5) NULL ,
	[PLZ_LAGER_F] [char] (5) NULL 
) 
GO

CREATE TABLE [dbo].[ORT_DA] (
	[Version_Dateikennung] [char] (2) NOT NULL ,
	[Version_Datum] [char] (4) NOT NULL ,
	[Version_Nr] [char] (3) NOT NULL ,
	[Datum] [char] (8) NOT NULL ,
	[ALORT] [char] (8) NOT NULL ,
	[STATUS] [char] (1) NOT NULL ,
	[ONAME] [varchar] (40) NOT NULL ,
	[ONAME_POST] [varchar] (40) NULL ,
	[OZUSATZ] [varchar] (30) NULL ,
	[ART_OZUSATZ] [char] (1) NOT NULL ,
	[ONAME24] [varchar] (24) NOT NULL ,
	[KGS] [char] (8) NULL ,
	[ALORT_NEU] [char] (8) NULL 
) 
GO

CREATE TABLE [dbo].[OTL_DA] (
	[Version_Dateikennung] [char] (2) NOT NULL ,
	[Version_Datum] [char] (4) NOT NULL ,
	[Version_Nr] [char] (3) NOT NULL ,
	[Datum] [char] (8) NOT NULL ,
	[ALORT] [char] (8) NOT NULL ,
	[SCHL] [char] (3) NOT NULL ,
	[PLZ] [char] (5) NOT NULL ,
	[STATUS] [char] (1) NOT NULL ,
	[STVERZ] [char] (1) NOT NULL ,
	[NAME] [varchar] (40) NOT NULL ,
	[KGS] [char] (8) NOT NULL 
) 
GO

CREATE TABLE [dbo].[PLZ_DA] (
	[Version_Dateikennung] [char] (2) NOT NULL ,
	[Version_Datum] [char] (4) NOT NULL ,
	[Version_Nr] [char] (3) NOT NULL ,
	[Datum] [char] (8) NOT NULL ,
	[PLZ] [char] (5) NOT NULL ,
	[ALORT] [char] (8) NOT NULL ,
	[ART_Kardinalitaet] [char] (1) NOT NULL ,
	[ART_Auslieferung] [char] (1) NOT NULL ,
	[STVerz] [char] (1) NOT NULL ,
	[PFVerz] [char] (1) NOT NULL ,
	[OName] [varchar] (40) NOT NULL ,
	[OZusatz] [varchar] (30) NULL ,
	[Art_Ozusatz] [char] (1) NOT NULL ,
	[OName24] [varchar] (24) NOT NULL ,
	[Postlag] [char] (1) NOT NULL ,
	[LA_Brief] [char] (8) NOT NULL ,
	[LA_Alort] [char] (8) NULL ,
	[KGS] [char] (8) NOT NULL ,
	[Ort_Code] [char] (3) NULL ,
	[Leitcode_Max] [char] (3) NULL ,
	[Rabatt_Info_Schwer] [char] (1) NOT NULL ,
	[Reserve] [char] (2) NULL ,
	[FZ_NR] [char] (2) NOT NULL ,
	[BZ_NR] [char] (2) NOT NULL 
) 
GO

CREATE TABLE [dbo].[POFA_DA] (
	[Version_Dateikennung] [char] (2) NOT NULL ,
	[Version_Datum] [char] (4) NOT NULL ,
	[Version_NR] [char] (3) NOT NULL ,
	[Datum] [char] (8) NOT NULL ,
	[ALORT] [char] (8) NOT NULL ,
	[PFNRVON] [char] (6) NOT NULL ,
	[PFNRBIS] [char] (6) NOT NULL ,
	[STATUS] [char] (1) NOT NULL ,
	[ALORG] [char] (8) NOT NULL ,
	[PFVERZ] [char] (1) NOT NULL ,
	[PLZ] [char] (5) NOT NULL ,
	[PLZ_ART_AUSLIEFERUNG] [char] (1) NOT NULL ,
	[PFNRVON_NEU] [char] (6) NULL ,
	[PFNRBIS_NEU] [char] (6) NULL 
) 
GO

CREATE TABLE [dbo].[PRUEF_DA] (
	[Version_Dateikennung] [char] (2) NULL ,
	[Version_Datum] [char] (4) NULL ,
	[Version_Nr] [char] (3) NULL ,
	[Dateikennung] [char] (2) NULL ,
	[Status] [char] (1) NULL ,
	[Satzzahl] [int] NULL ,
	[Datum_Summe] [char] (16) NULL 
) 
GO

CREATE TABLE [dbo].[STR_DA] (
	[Version_Dateikennung] [char] (2) NOT NULL ,
	[Version_Datum] [char] (4) NOT NULL ,
	[Version_Nr] [char] (3) NOT NULL ,
	[Datum] [char] (8) NOT NULL ,
	[Alort] [char] (8) NOT NULL ,
	[Namen_Schluessel] [char] (6) NOT NULL ,
	[Bund_LFDNR] [char] (5) NOT NULL ,
	[HNRVON] [char] (8) NOT NULL ,
	[HNRBIS] [char] (8) NOT NULL ,
	[Status] [char] (1) NOT NULL ,
	[HNR_1000] [char] (1) NOT NULL ,
	[STVerz] [char] (1) NOT NULL ,
	[Name_Sort] [varchar] (46) NOT NULL ,
	[Name46] [varchar] (46) NOT NULL ,
	[Name22] [varchar] (22) NOT NULL ,
	[Reserve] [char] (1) NULL ,
	[HNR_Typ] [char] (1) NOT NULL ,
	[PLZ] [char] (5) NOT NULL ,
	[Code] [char] (3) NULL ,
	[OTL_Schl] [char] (3) NULL ,
	[ALORG_B] [char] (8) NOT NULL ,
	[KGS] [char] (8) NOT NULL ,
	[ALORT_Neu] [char] (8) NOT NULL ,
	[Namen_Schluessel_Neu] [char] (6) NOT NULL ,
	[Bund_Lfdnr_neu] [char] (5) NOT NULL ,
	[HNRVON_NEU] [char] (8) NOT NULL ,
	[HNRBIS_NEU] [char] (8) NOT NULL 
) 
GO

ALTER TABLE [dbo].[GROSS_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_GROSS_DA] PRIMARY KEY  CLUSTERED 
	(
		[ID_NR]
	)  
GO

ALTER TABLE [dbo].[KGS_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_KGS_DA] PRIMARY KEY  CLUSTERED 
	(
		[schluessel],
		[satzart]
	)  
GO

ALTER TABLE [dbo].[ORG_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_ORG_DA] PRIMARY KEY  CLUSTERED 
	(
		[ALOrg]
	) 
GO

ALTER TABLE [dbo].[ORT_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_ORT_DA] PRIMARY KEY  CLUSTERED 
	(
		[ALORT],
		[STATUS]
	)  
GO

ALTER TABLE [dbo].[OTL_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_OTL_DA] PRIMARY KEY  CLUSTERED 
	(
		[ALORT],
		[SCHL],
		[PLZ],
		[STATUS]
	) 
GO

ALTER TABLE [dbo].[PLZ_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_PLZ_DA] PRIMARY KEY  CLUSTERED 
	(
		[PLZ],
		[ALORT]
	)  
GO

ALTER TABLE [dbo].[POFA_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_POFA_DA] PRIMARY KEY  CLUSTERED 
	(
		[ALORT],
		[PFNRVON],
		[PFNRBIS],
		[STATUS],
		[ALORG]
	) 
GO

ALTER TABLE [dbo].[STR_DA] WITH NOCHECK ADD 
	CONSTRAINT [PK_STR_DA] PRIMARY KEY  CLUSTERED 
	(
		[Alort],
		[Namen_Schluessel],
		[Bund_LFDNR],
		[HNRVON],
		[HNRBIS],
		[Status]
	)  
GO

 CREATE  INDEX [IX_Name_Sort] ON [dbo].[STR_DA]([Name_Sort]) WITH  FILLFACTOR = 100
GO

 CREATE  INDEX [IX_Name46] ON [dbo].[STR_DA]([Name46]) WITH  FILLFACTOR = 100 
GO

 CREATE  INDEX [IX_KGS] ON [dbo].[STR_DA]([KGS]) WITH  FILLFACTOR = 100 
GO