When you use the Schema Engine to create your schema engine it generates MSSQL Scripts internally and executes them. You can also generate these SQL scripts yourself (see Creating Tables).
Important Note: The SQL Scripts are provided for your reference only. You should not attempt to manually create a schema yourself by generating the SQL scripts and then executing them in a SQL Query tool. The Schema Engine, in addition to creating the tables will also populate some of the master tables with information needed for the schema processors to run correctly.
These are the master tables used by the schema engine
/* Add The Database Role */ if NOT exists (Select * from Sysusers where [name]='PR1_DBAROLE') exec sp_addrole 'PR1_DBAROLE' GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[PR1_HL7Data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Drop table [dbo].[PR1_HL7Data] End GO CREATE TABLE [dbo].[PR1_HL7Data] ( [MessageID] [Varchar] (50) Not Null PRIMARY KEY, [VendorName] [VarChar] (50) Not Null, [VendorVersion] [VarChar] (25) Not Null, [MsgControl] [VarChar] (50) Not Null, [PartnerAPP] [Varchar] (60) Not Null, [DateLoaded] DateTime Default GetDate(), [LastLoaded] DateTime Default GetDate(), [LoadCount] Int Default 1, [MsgType] [VarChar] (20) Not Null, [MsgEvent] [VarChar] (20) Null, [Outbound] [Int] Not Null Default 0, [Inbound] [Int] Not Null Default 0, [Processed] [Int] Not Null Default 0, [Warnings] [Int] Null Default 0, [Loaded] [Int] Not Null Default 0, [SchemaLoaded] [Int] Not Null Default 0, [StatusMessage] [VarChar] (1024) Null, [ArchiveID] [VarChar] (50) Null, [HL7Format] [Int] Default 0, [SegmentCount] Int Default 1, [MessageSize] Int Default 0, [HL7Message] Text Null ) GO Create Index PR1_HL7_AppControl ON PR1_HL7Data(MsgControl,PartnerApp) GO Grant All On PR1_HL7Data To PR1_DBAROLE GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[PR1_MessageErrors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Drop table [dbo].[PR1_MessageErrors] End GO CREATE TABLE [dbo].[PR1_MessageErrors] ( [MessageID] [Varchar] (50) Not Null, [DateLoaded] DateTime Default GetDate(), [ErrorMessage] [Varchar] (1024) Null ) GO Create Index PR1_MsgID on PR1_MessageErrors(MessageID) GO Grant All On PR1_MessageErrors To PR1_DBAROLE GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[PR1_SchemaMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Drop table [dbo].[PR1_SchemaMaster] End GO CREATE TABLE [dbo].[PR1_SchemaMaster] ( [Schema_ID] [Varchar] (50) Not Null PRIMARY KEY, [Schema_VendorID] [Varchar] (50) Not Null, [Maint_LastRun] DateTime Default GetDate(), [Created] DateTime Default GetDate(), [VendorDefinition] Text Null ) GO Grant All On PR1_MessageErrors To PR1_DBAROLE GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[PR1_MessageManifest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Drop table [dbo].[PR1_MessageManifest] End GO CREATE TABLE [dbo].[PR1_MessageManifest] ( [MessageID] [Varchar] (50) Not Null, [SegmentName] [VarChar] (10) Not Null, [SegmentIDX] [int] Not Null ) GO Create Unique Index PR1_Manifest on PR1_MessageManifest(MessageID,SegmentIDX) GO Grant All On PR1_MessageManifest To PR1_DBAROLE GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[PR1_InterfaceTables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Drop table [dbo].[PR1_InterfaceTables] End GO CREATE TABLE [dbo].[PR1_InterfaceTables] ( [SegmentName] [VarChar] (10) Not Null PRIMARY KEY, [TableNames] [VarChar] (75) Not Null, [Hl7FieldCount] [int] Not Null, [Description] [VarChar] (1024) Null )
GO Grant All On PR1_InterfaceTables To PR1_DBAROLE GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[PR1_InterfaceFields]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Drop table [dbo].[PR1_InterfaceFields] End GO CREATE TABLE [dbo].[PR1_InterfaceFields] ( [Record_GUID] [Varchar] (50) Not Null Default newid() PRIMARY KEY, [SegmentName] [VarChar] (10) Not Null, [TableName] [VarChar] (50) Not Null, [FieldNumber] [int] Not Null, [Components] [int] Not Null, [Repeats] [int] Not Null, [Description] [VarChar] (1024) Null )
GO Grant All On PR1_InterfaceFields To PR1_DBAROLE GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[PR1_InterfaceComponents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN Drop table [dbo].[PR1_InterfaceComponents] End GO CREATE TABLE [dbo].[PR1_InterfaceComponents] ( [Record_GUID] [Varchar] (50) Not Null Default newid() PRIMARY KEY, [SegmentName] [VarChar] (10) Not Null, [FieldNumber] [int] Not Null, [FieldName] [VarChar] (30) Not Null, [Component] [int] Not Null, [SubComponents] [int] Not Null, [Description] [VarChar] (1024) Null )
GO Grant All On PR1_InterfaceComponents To PR1_DBAROLE GO |
This is a partial example of the SQL generated to create some of the HL7 segment database tables for a schema with a prefix of PR1 based on the default HL7 Version 2.3 vendor definition.
Table names are dynamically created based on the schema prefix, the HL7 segment name and the number of HL7 fields the segment contains. Like so:
<PREFIX>_Segment_<HL7SEGMENTNAME>_A
If a segment contains 40 or more HL7 data fields it will be split into 2 tables with the data in fields 1-39 going into table <PREFIX>_Segment_<HL7SEGMENTNAME>_A and fields 40 to <fieldcount> going into table <PREFIX>_Segment_<HL7SEGMENTNAME>_B
/*Drop Table PR1_Segment_ACC_A */ If Exists(Select * From sysobjects where id = object_id(N'[dbo].[PR1_Segment_ACC_A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Drop Table [dbo].[PR1_Segment_ACC_A] GO
/* Create the table for the ACC segment */ CREATE TABLE [dbo].[PR1_Segment_ACC_A] ( [MessageID] [varchar] (50) NOT NULL, [IDX] [Int] NOT NULL, [ACC_F1_C1] [varchar] (26) NULL, [ACC_F1_C2] [varchar] (26) NULL, [ACC_F2_C1] [varchar] (40) NULL, [ACC_F2_C2] [varchar] (40) NULL, [ACC_F2_C3] [varchar] (40) NULL, [ACC_F2_C4] [varchar] (40) NULL, [ACC_F2_C5] [varchar] (40) NULL, [ACC_F2_C6] [varchar] (40) NULL, [ACC_F3_C1] [varchar] (25) NULL, [ACC_F4_C1] [varchar] (40) NULL, [ACC_F4_C2] [varchar] (40) NULL, [ACC_F4_C3] [varchar] (40) NULL, [ACC_F4_C4] [varchar] (40) NULL, [ACC_F4_C5] [varchar] (40) NULL, [ACC_F4_C6] [varchar] (40) NULL, [ACC_F5_C1] [varchar] (10) NULL, [ACC_F6_C1] [varchar] (12) NULL ) GO Create UNIQUE Index EH_ACC1_MSGIDX on PR1_Segment_ACC_A(MessageID,IDX) GO GRANT ALL ON PR1_Segment_ACC_A TO PR1_DBAROLE GO
/*Drop Table PR1_Segment_AIL_A */ If Exists(Select * From sysobjects where id = object_id(N'[dbo].[PR1_Segment_AIL_A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Drop Table [dbo].[PR1_Segment_AIL_A] GO
/* Create the table for the AIL segment */ CREATE TABLE [dbo].[PR1_Segment_AIL_A] ( [MessageID] [varchar] (50) NOT NULL, [IDX] [Int] NOT NULL, [AIL_F1_C1] [varchar] (10) NULL, [AIL_F2_C1] [varchar] (10) NULL, [AIL_F3_C1] [varchar] (20) NULL, [AIL_F3_C2] [varchar] (20) NULL, [AIL_F3_C3] [varchar] (20) NULL, [AIL_F3_C4] [varchar] (20) NULL, [AIL_F3_C5] [varchar] (20) NULL, [AIL_F3_C6] [varchar] (20) NULL, [AIL_F3_C7] [varchar] (20) NULL, [AIL_F3_C8] [varchar] (20) NULL, [AIL_F3_C9] [varchar] (20) NULL, [AIL_F4_C1] [varchar] (40) NULL, [AIL_F4_C2] [varchar] (40) NULL, [AIL_F4_C3] [varchar] (40) NULL, [AIL_F4_C4] [varchar] (40) NULL, [AIL_F4_C5] [varchar] (40) NULL, [AIL_F4_C6] [varchar] (40) NULL, [AIL_F5_C1] [varchar] (40) NULL, [AIL_F5_C2] [varchar] (40) NULL, [AIL_F5_C3] [varchar] (40) NULL, [AIL_F5_C4] [varchar] (40) NULL, [AIL_F5_C5] [varchar] (40) NULL, [AIL_F5_C6] [varchar] (40) NULL, [AIL_F6_C1] [varchar] (26) NULL, [AIL_F6_C2] [varchar] (26) NULL, [AIL_F7_C1] [varchar] (20) NULL, [AIL_F8_C1] [varchar] (40) NULL, [AIL_F8_C2] [varchar] (40) NULL, [AIL_F8_C3] [varchar] (40) NULL, [AIL_F8_C4] [varchar] (40) NULL, [AIL_F8_C5] [varchar] (40) NULL, [AIL_F8_C6] [varchar] (40) NULL, [AIL_F9_C1] [varchar] (20) NULL, [AIL_F10_C1] [varchar] (40) NULL, [AIL_F10_C2] [varchar] (40) NULL, [AIL_F10_C3] [varchar] (40) NULL, [AIL_F10_C4] [varchar] (40) NULL, [AIL_F10_C5] [varchar] (40) NULL, [AIL_F10_C6] [varchar] (40) NULL, [AIL_F11_C1] [varchar] (10) NULL, [AIL_F12_C1] [varchar] (40) NULL, [AIL_F12_C2] [varchar] (40) NULL, [AIL_F12_C3] [varchar] (40) NULL, [AIL_F12_C4] [varchar] (40) NULL, [AIL_F12_C5] [varchar] (40) NULL, [AIL_F12_C6] [varchar] (40) NULL ) GO Create UNIQUE Index EH_AIL1_MSGIDX on PR1_Segment_AIL_A(MessageID,IDX) GO GRANT ALL ON PR1_Segment_AIL_A TO PR1_DBAROLE GO
/*Drop Table PR1_Segment_AIP_A */ If Exists(Select * From sysobjects where id = object_id(N'[dbo].[PR1_Segment_AIP_A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Drop Table [dbo].[PR1_Segment_AIP_A] GO
/* Create the table for the AIP segment */ CREATE TABLE [dbo].[PR1_Segment_AIP_A] ( [MessageID] [varchar] (50) NOT NULL, [IDX] [Int] NOT NULL, [AIP_F1_C1] [varchar] (10) NULL, [AIP_F2_C1] [varchar] (10) NULL, [AIP_F3_C1] [varchar] (35) NULL, [AIP_F3_C2] [varchar] (35) NULL, [AIP_F3_C3] [varchar] (35) NULL, [AIP_F3_C4] [varchar] (35) NULL, [AIP_F3_C5] [varchar] (35) NULL, [AIP_F3_C6] [varchar] (35) NULL, [AIP_F3_C7] [varchar] (35) NULL, [AIP_F4_C1] [varchar] (40) NULL, [AIP_F4_C2] [varchar] (40) NULL, [AIP_F4_C3] [varchar] (40) NULL, [AIP_F4_C4] [varchar] (40) NULL, [AIP_F4_C5] [varchar] (40) NULL, [AIP_F4_C6] [varchar] (40) NULL, [AIP_F5_C1] [varchar] (40) NULL, [AIP_F5_C2] [varchar] (40) NULL, [AIP_F5_C3] [varchar] (40) NULL, [AIP_F5_C4] [varchar] (40) NULL, [AIP_F5_C5] [varchar] (40) NULL, [AIP_F5_C6] [varchar] (40) NULL, [AIP_F6_C1] [varchar] (26) NULL, [AIP_F6_C2] [varchar] (26) NULL, [AIP_F7_C1] [varchar] (20) NULL, [AIP_F8_C1] [varchar] (40) NULL, [AIP_F8_C2] [varchar] (40) NULL, [AIP_F8_C3] [varchar] (40) NULL, [AIP_F8_C4] [varchar] (40) NULL, [AIP_F8_C5] [varchar] (40) NULL, [AIP_F8_C6] [varchar] (40) NULL, [AIP_F9_C1] [varchar] (20) NULL, [AIP_F10_C1] [varchar] (40) NULL, [AIP_F10_C2] [varchar] (40) NULL, [AIP_F10_C3] [varchar] (40) NULL, [AIP_F10_C4] [varchar] (40) NULL, [AIP_F10_C5] [varchar] (40) NULL, [AIP_F10_C6] [varchar] (40) NULL, [AIP_F11_C1] [varchar] (10) NULL, [AIP_F12_C1] [varchar] (40) NULL, [AIP_F12_C2] [varchar] (40) NULL, [AIP_F12_C3] [varchar] (40) NULL, [AIP_F12_C4] [varchar] (40) NULL, [AIP_F12_C5] [varchar] (40) NULL, [AIP_F12_C6] [varchar] (40) NULL ) GO Create UNIQUE Index EH_AIP1_MSGIDX on PR1_Segment_AIP_A(MessageID,IDX) GO GRANT ALL ON PR1_Segment_AIP_A TO PR1_DBAROLE GO
/* Create the table for the GT1 segment */ CREATE TABLE [dbo].[PR1_Segment_GT1_A] ( [MessageID] [varchar] (50) NOT NULL, [IDX] [Int] NOT NULL, [GT1_F1_C1] [varchar] (10) NULL, [GT1_F2_C1] [varchar] (59) NULL, [GT1_F2_C2] [varchar] (59) NULL, [GT1_F2_C3] [varchar] (20) NULL, [GT1_F2_C4] [varchar] (20) NULL, [GT1_F2_C5] [varchar] (20) NULL, [GT1_F2_C6] [varchar] (20) NULL, [GT1_F3_C1] [varchar] (35) NULL, [GT1_F3_C2] [varchar] (35) NULL, [GT1_F3_C3] [varchar] (35) NULL, [GT1_F3_C4] [varchar] (35) NULL, [GT1_F3_C5] [varchar] (35) NULL, [GT1_F3_C6] [varchar] (35) NULL, [GT1_F3_C7] [varchar] (20) NULL, [GT1_F4_C1] [varchar] (35) NULL, [GT1_F4_C2] [varchar] (35) NULL, [GT1_F4_C3] [varchar] (35) NULL, [GT1_F4_C4] [varchar] (35) NULL, [GT1_F4_C5] [varchar] (35) NULL, [GT1_F4_C6] [varchar] (35) NULL, [GT1_F4_C7] [varchar] (20) NULL, [GT1_F5_C1] [varchar] (40) NULL, [GT1_F5_C2] [varchar] (40) NULL, [GT1_F5_C3] [varchar] (40) NULL, [GT1_F5_C4] [varchar] (40) NULL, [GT1_F5_C5] [varchar] (40) NULL, [GT1_F5_C6] [varchar] (20) NULL, [GT1_F5_C7] [varchar] (20) NULL, [GT1_F5_C8] [varchar] (20) NULL, [GT1_F5_C9] [varchar] (20) NULL, [GT1_F6_C1] [varchar] (20) NULL, [GT1_F6_C2] [varchar] (20) NULL, [GT1_F6_C3] [varchar] (20) NULL, [GT1_F6_C4] [varchar] (20) NULL, [GT1_F6_C5] [varchar] (20) NULL, [GT1_F6_C6] [varchar] (20) NULL, [GT1_F6_C7] [varchar] (20) NULL, [GT1_F6_C8] [varchar] (20) NULL, [GT1_F7_C1] [varchar] (20) NULL, [GT1_F7_C2] [varchar] (20) NULL, [GT1_F7_C3] [varchar] (20) NULL, [GT1_F7_C4] [varchar] (20) NULL, [GT1_F7_C5] [varchar] (20) NULL, [GT1_F7_C6] [varchar] (20) NULL, [GT1_F7_C7] [varchar] (20) NULL, [GT1_F7_C8] [varchar] (20) NULL, [GT1_F8_C1] [varchar] (26) NULL, [GT1_F8_C2] [varchar] (26) NULL, [GT1_F9_C1] [varchar] (10) NULL, [GT1_F10_C1] [varchar] (10) NULL, [GT1_F11_C1] [varchar] (40) NULL, [GT1_F11_C2] [varchar] (40) NULL, [GT1_F11_C3] [varchar] (40) NULL, [GT1_F11_C4] [varchar] (40) NULL, [GT1_F11_C5] [varchar] (40) NULL, [GT1_F11_C6] [varchar] (40) NULL, [GT1_F12_C1] [varchar] (11) NULL, [GT1_F13_C1] [varchar] (10) NULL, [GT1_F14_C1] [varchar] (10) NULL, [GT1_F15_C1] [varchar] (10) NULL, [GT1_F16_C1] [varchar] (35) NULL, [GT1_F16_C2] [varchar] (35) NULL, [GT1_F16_C3] [varchar] (35) NULL, [GT1_F16_C4] [varchar] (35) NULL, [GT1_F16_C5] [varchar] (35) NULL, [GT1_F16_C6] [varchar] (35) NULL, [GT1_F16_C7] [varchar] (20) NULL, [GT1_F17_C1] [varchar] (40) NULL, [GT1_F17_C2] [varchar] (40) NULL, [GT1_F17_C3] [varchar] (40) NULL, [GT1_F17_C4] [varchar] (40) NULL, [GT1_F17_C5] [varchar] (40) NULL, [GT1_F17_C6] [varchar] (20) NULL, [GT1_F17_C7] [varchar] (20) NULL, [GT1_F17_C8] [varchar] (20) NULL, [GT1_F17_C9] [varchar] (20) NULL, [GT1_F18_C1] [varchar] (20) NULL, [GT1_F18_C2] [varchar] (20) NULL, [GT1_F18_C3] [varchar] (20) NULL, [GT1_F18_C4] [varchar] (20) NULL, [GT1_F18_C5] [varchar] (20) NULL, [GT1_F18_C6] [varchar] (20) NULL, [GT1_F18_C7] [varchar] (20) NULL, [GT1_F18_C8] [varchar] (20) NULL, [GT1_F19_C1] [varchar] (20) NULL, [GT1_F19_C2] [varchar] (20) NULL, [GT1_F19_C3] [varchar] (20) NULL, [GT1_F19_C4] [varchar] (20) NULL, [GT1_F19_C5] [varchar] (20) NULL, [GT1_F19_C6] [varchar] (20) NULL, [GT1_F20_C1] [varchar] (10) NULL, [GT1_F21_C1] [varchar] (50) NULL, [GT1_F21_C2] [varchar] (20) NULL, [GT1_F21_C3] [varchar] (20) NULL, [GT1_F21_C4] [varchar] (20) NULL, [GT1_F21_C5] [varchar] (20) NULL, [GT1_F21_C6] [varchar] (20) NULL, [GT1_F21_C7] [varchar] (20) NULL, [GT1_F21_C8] [varchar] (20) NULL, [GT1_F22_C1] [varchar] (10) NULL, [GT1_F23_C1] [varchar] (40) NULL, [GT1_F23_C2] [varchar] (40) NULL, [GT1_F23_C3] [varchar] (40) NULL, [GT1_F23_C4] [varchar] (40) NULL, [GT1_F23_C5] [varchar] (40) NULL, [GT1_F23_C6] [varchar] (40) NULL, [GT1_F24_C1] [varchar] (26) NULL, [GT1_F24_C2] [varchar] (26) NULL, [GT1_F25_C1] [varchar] (10) NULL, [GT1_F26_C1] [varchar] (40) NULL, [GT1_F26_C2] [varchar] (40) NULL, [GT1_F26_C3] [varchar] (40) NULL, [GT1_F26_C4] [varchar] (40) NULL, [GT1_F26_C5] [varchar] (40) NULL, [GT1_F26_C6] [varchar] (40) NULL, [GT1_F27_C1] [varchar] (10) NULL, [GT1_F27_C2] [varchar] (10) NULL, [GT1_F27_C3] [varchar] (10) NULL, [GT1_F27_C4] [varchar] (10) NULL, [GT1_F27_C5] [varchar] (10) NULL, [GT1_F27_C6] [varchar] (10) NULL, [GT1_F28_C1] [varchar] (10) NULL, [GT1_F29_C1] [varchar] (20) NULL, [GT1_F29_C2] [varchar] (20) NULL, [GT1_F29_C3] [varchar] (20) NULL, [GT1_F29_C4] [varchar] (20) NULL, [GT1_F29_C5] [varchar] (20) NULL, [GT1_F29_C6] [varchar] (20) NULL, [GT1_F30_C1] [varchar] (40) NULL, [GT1_F30_C2] [varchar] (40) NULL, [GT1_F30_C3] [varchar] (40) NULL, [GT1_F30_C4] [varchar] (40) NULL, [GT1_F30_C5] [varchar] (40) NULL, [GT1_F30_C6] [varchar] (40) NULL, [GT1_F31_C1] [varchar] (10) NULL, [GT1_F32_C1] [varchar] (10) NULL, [GT1_F33_C1] [varchar] (10) NULL, [GT1_F34_C1] [varchar] (10) NULL, [GT1_F35_C1] [varchar] (40) NULL, [GT1_F35_C2] [varchar] (40) NULL, [GT1_F35_C3] [varchar] (40) NULL, [GT1_F35_C4] [varchar] (40) NULL, [GT1_F35_C5] [varchar] (40) NULL, [GT1_F35_C6] [varchar] (40) NULL, [GT1_F36_C1] [varchar] (40) NULL, [GT1_F36_C2] [varchar] (40) NULL, [GT1_F36_C3] [varchar] (40) NULL, [GT1_F36_C4] [varchar] (40) NULL, [GT1_F36_C5] [varchar] (40) NULL, [GT1_F36_C6] [varchar] (40) NULL, [GT1_F37_C1] [varchar] (10) NULL, [GT1_F38_C1] [varchar] (40) NULL, [GT1_F38_C2] [varchar] (40) NULL, [GT1_F38_C3] [varchar] (40) NULL, [GT1_F38_C4] [varchar] (40) NULL, [GT1_F38_C5] [varchar] (40) NULL, [GT1_F38_C6] [varchar] (40) NULL, [GT1_F39_C1] [varchar] (10) NULL, [GT1_F40_C1] [varchar] (10) NULL ) GO Create UNIQUE Index EH_GT11_MSGIDX on PR1_Segment_GT1_A(MessageID,IDX) GO GRANT ALL ON PR1_Segment_GT1_A TO PR1_DBAROLE GO CREATE TABLE [dbo].[PR1_Segment_GT1_B] ( [MessageID] [varchar] (50) NOT NULL, [IDX] [Int] NOT NULL, [GT1_F41_C1] [varchar] (40) NULL, [GT1_F41_C2] [varchar] (40) NULL, [GT1_F41_C3] [varchar] (40) NULL, [GT1_F41_C4] [varchar] (40) NULL, [GT1_F41_C5] [varchar] (40) NULL, [GT1_F41_C6] [varchar] (40) NULL, [GT1_F42_C1] [varchar] (35) NULL, [GT1_F42_C2] [varchar] (35) NULL, [GT1_F42_C3] [varchar] (35) NULL, [GT1_F42_C4] [varchar] (35) NULL, [GT1_F42_C5] [varchar] (35) NULL, [GT1_F42_C6] [varchar] (35) NULL, [GT1_F42_C7] [varchar] (20) NULL, [GT1_F43_C1] [varchar] (40) NULL, [GT1_F43_C2] [varchar] (40) NULL, [GT1_F43_C3] [varchar] (40) NULL, [GT1_F43_C4] [varchar] (40) NULL, [GT1_F43_C5] [varchar] (40) NULL, [GT1_F43_C6] [varchar] (40) NULL, [GT1_F44_C1] [varchar] (40) NULL, [GT1_F44_C2] [varchar] (40) NULL, [GT1_F44_C3] [varchar] (40) NULL, [GT1_F44_C4] [varchar] (40) NULL, [GT1_F44_C5] [varchar] (40) NULL, [GT1_F44_C6] [varchar] (40) NULL, [GT1_F45_C1] [varchar] (35) NULL, [GT1_F45_C2] [varchar] (35) NULL, [GT1_F45_C3] [varchar] (35) NULL, [GT1_F45_C4] [varchar] (35) NULL, [GT1_F45_C5] [varchar] (35) NULL, [GT1_F45_C6] [varchar] (35) NULL, [GT1_F45_C7] [varchar] (20) NULL, [GT1_F46_C1] [varchar] (20) NULL, [GT1_F46_C2] [varchar] (20) NULL, [GT1_F46_C3] [varchar] (20) NULL, [GT1_F46_C4] [varchar] (20) NULL, [GT1_F46_C5] [varchar] (20) NULL, [GT1_F46_C6] [varchar] (20) NULL, [GT1_F46_C7] [varchar] (20) NULL, [GT1_F46_C8] [varchar] (20) NULL, [GT1_F47_C1] [varchar] (40) NULL, [GT1_F47_C2] [varchar] (40) NULL, [GT1_F47_C3] [varchar] (40) NULL, [GT1_F47_C4] [varchar] (40) NULL, [GT1_F47_C5] [varchar] (40) NULL, [GT1_F47_C6] [varchar] (40) NULL, [GT1_F48_C1] [varchar] (10) NULL, [GT1_F49_C1] [varchar] (20) NULL, [GT1_F50_C1] [varchar] (20) NULL, [GT1_F50_C2] [varchar] (20) NULL, [GT1_F51_C1] [varchar] (50) NULL, [GT1_F51_C2] [varchar] (20) NULL, [GT1_F51_C3] [varchar] (20) NULL, [GT1_F51_C4] [varchar] (20) NULL, [GT1_F51_C5] [varchar] (20) NULL, [GT1_F51_C6] [varchar] (20) NULL, [GT1_F51_C7] [varchar] (20) NULL, [GT1_F51_C8] [varchar] (20) NULL, [GT1_F52_C1] [varchar] (10) NULL, [GT1_F53_C1] [varchar] (10) NULL, [GT1_F54_C1] [varchar] (20) NULL, [GT1_F54_C2] [varchar] (20) NULL, [GT1_F55_C1] [varchar] (40) NULL, [GT1_F55_C2] [varchar] (40) NULL, [GT1_F55_C3] [varchar] (40) NULL, [GT1_F55_C4] [varchar] (40) NULL, [GT1_F55_C5] [varchar] (40) NULL, [GT1_F55_C6] [varchar] (40) NULL ) GO Create UNIQUE Index EH_GT12_MSGIDX on PR1_Segment_GT1_B(MessageID,IDX) GO GRANT ALL ON PR1_Segment_GT1_B TO PR1_DBAROLE GO
/*Drop Table PR1_Segment_MSH_A */ If Exists(Select * From sysobjects where id = object_id(N'[dbo].[PR1_Segment_MSH_A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Drop Table [dbo].[PR1_Segment_MSH_A] GO
/* Create the table for the MSH segment */ CREATE TABLE [dbo].[PR1_Segment_MSH_A] ( [MessageID] [varchar] (50) NOT NULL, [IDX] [Int] NOT NULL, [MSH_F1_C1] [varchar] (50) NULL, [MSH_F2_C1] [varchar] (50) NULL, [MSH_F3_C1] [varchar] (50) NULL, [MSH_F3_C2] [varchar] (50) NULL, [MSH_F3_C3] [varchar] (50) NULL, [MSH_F4_C1] [varchar] (50) NULL, [MSH_F4_C2] [varchar] (50) NULL, [MSH_F4_C3] [varchar] (50) NULL, [MSH_F5_C1] [varchar] (50) NULL, [MSH_F5_C2] [varchar] (50) NULL, [MSH_F5_C3] [varchar] (50) NULL, [MSH_F6_C1] [varchar] (50) NULL, [MSH_F6_C2] [varchar] (50) NULL, [MSH_F6_C3] [varchar] (50) NULL, [MSH_F7_C1] [varchar] (50) NULL, [MSH_F7_C2] [varchar] (50) NULL, [MSH_F8_C1] [varchar] (50) NULL, [MSH_F9_C1] [varchar] (50) NULL, [MSH_F9_C2] [varchar] (50) NULL, [MSH_F9_C3] [varchar] (50) NULL, [MSH_F9_C4] [varchar] (50) NULL, [MSH_F10_C1] [varchar] (50) NULL, [MSH_F11_C1] [varchar] (50) NULL, [MSH_F11_C2] [varchar] (50) NULL, [MSH_F12_C1] [varchar] (50) NULL, [MSH_F12_C2] [varchar] (50) NULL, [MSH_F12_C3] [varchar] (50) NULL, [MSH_F13_C1] [varchar] (50) NULL, [MSH_F14_C1] [varchar] (180) NULL, [MSH_F15_C1] [varchar] (50) NULL, [MSH_F16_C1] [varchar] (50) NULL, [MSH_F17_C1] [varchar] (50) NULL, [MSH_F18_C1] [varchar] (50) NULL, [MSH_F19_C1] [varchar] (50) NULL, [MSH_F19_C2] [varchar] (50) NULL, [MSH_F19_C3] [varchar] (50) NULL, [MSH_F19_C4] [varchar] (50) NULL, [MSH_F19_C5] [varchar] (50) NULL, [MSH_F19_C6] [varchar] (50) NULL, [MSH_F20_C1] [varchar] (50) NULL ) GO Create UNIQUE Index EH_MSH1_MSGIDX on PR1_Segment_MSH_A(MessageID,IDX) GO Create Index IDX_MSH_F9_C1 on PR1_Segment_MSH_A(MSH_F9_C1) GO Create Index IDX_MSH_F9_C2 on PR1_Segment_MSH_A(MSH_F9_C2) GO Create Index IDX_MSH_F10_C1 on PR1_Segment_MSH_A(MSH_F10_C1) GO GRANT ALL ON PR1_Segment_MSH_A TO PR1_DBAROLE GO
/*Drop Table PR1_Segment_PID_A */ If Exists(Select * From sysobjects where id = object_id(N'[dbo].[PR1_Segment_PID_A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Drop Table [dbo].[PR1_Segment_PID_A] GO
/* Create the table for the PID segment */ CREATE TABLE [dbo].[PR1_Segment_PID_A] ( [MessageID] [varchar] (50) NOT NULL, [IDX] [Int] NOT NULL, [PID_F1_C1] [varchar] (10) NULL, [PID_F2_C1] [varchar] (20) NULL, [PID_F2_C2] [varchar] (20) NULL, [PID_F2_C3] [varchar] (20) NULL, [PID_F2_C4] [varchar] (20) NULL, [PID_F2_C5] [varchar] (20) NULL, [PID_F2_C6] [varchar] (20) NULL, [PID_F3_C1] [varchar] (20) NULL, [PID_F3_C2] [varchar] (20) NULL, [PID_F3_C3] [varchar] (20) NULL, [PID_F3_C4] [varchar] (20) NULL, [PID_F3_C5] [varchar] (20) NULL, [PID_F3_C6] [varchar] (20) NULL, [PID_F4_C1] [varchar] (20) NULL, [PID_F4_C2] [varchar] (20) NULL, [PID_F4_C3] [varchar] (20) NULL, [PID_F4_C4] [varchar] (20) NULL, [PID_F4_C5] [varchar] (20) NULL, [PID_F4_C6] [varchar] (20) NULL, [PID_F5_C1] [varchar] (35) NULL, [PID_F5_C2] [varchar] (35) NULL, [PID_F5_C3] [varchar] (35) NULL, [PID_F5_C4] [varchar] (35) NULL, [PID_F5_C5] [varchar] (35) NULL, [PID_F5_C6] [varchar] (35) NULL, [PID_F5_C7] [varchar] (20) NULL, [PID_F6_C1] [varchar] (35) NULL, [PID_F6_C2] [varchar] (35) NULL, [PID_F6_C3] [varchar] (35) NULL, [PID_F6_C4] [varchar] (35) NULL, [PID_F6_C5] [varchar] (35) NULL, [PID_F6_C6] [varchar] (35) NULL, [PID_F6_C7] [varchar] (20) NULL, [PID_F7_C1] [varchar] (26) NULL, [PID_F7_C2] [varchar] (26) NULL, [PID_F8_C1] [varchar] (10) NULL, [PID_F9_C1] [varchar] (35) NULL, [PID_F9_C2] [varchar] (35) NULL, [PID_F9_C3] [varchar] (35) NULL, [PID_F9_C4] [varchar] (35) NULL, [PID_F9_C5] [varchar] (35) NULL, [PID_F9_C6] [varchar] (35) NULL, [PID_F9_C7] [varchar] (20) NULL, [PID_F10_C1] [varchar] (40) NULL, [PID_F10_C2] [varchar] (40) NULL, [PID_F10_C3] [varchar] (40) NULL, [PID_F10_C4] [varchar] (40) NULL, [PID_F10_C5] [varchar] (40) NULL, [PID_F10_C6] [varchar] (40) NULL, [PID_F11_C1] [varchar] (40) NULL, [PID_F11_C2] [varchar] (40) NULL, [PID_F11_C3] [varchar] (40) NULL, [PID_F11_C4] [varchar] (40) NULL, [PID_F11_C5] [varchar] (40) NULL, [PID_F11_C6] [varchar] (20) NULL, [PID_F11_C7] [varchar] (20) NULL, [PID_F11_C8] [varchar] (20) NULL, [PID_F11_C9] [varchar] (20) NULL, [PID_F12_C1] [varchar] (10) NULL, [PID_F13_C1] [varchar] (20) NULL, [PID_F13_C2] [varchar] (20) NULL, [PID_F13_C3] [varchar] (20) NULL, [PID_F13_C4] [varchar] (20) NULL, [PID_F13_C5] [varchar] (20) NULL, [PID_F13_C6] [varchar] (20) NULL, [PID_F13_C7] [varchar] (20) NULL, [PID_F13_C8] [varchar] (20) NULL, [PID_F14_C1] [varchar] (20) NULL, [PID_F14_C2] [varchar] (20) NULL, [PID_F14_C3] [varchar] (20) NULL, [PID_F14_C4] [varchar] (20) NULL, [PID_F14_C5] [varchar] (20) NULL, [PID_F14_C6] [varchar] (20) NULL, [PID_F14_C7] [varchar] (20) NULL, [PID_F14_C8] [varchar] (20) NULL, [PID_F15_C1] [varchar] (40) NULL, [PID_F15_C2] [varchar] (40) NULL, [PID_F15_C3] [varchar] (40) NULL, [PID_F15_C4] [varchar] (40) NULL, [PID_F15_C5] [varchar] (40) NULL, [PID_F15_C6] [varchar] (40) NULL, [PID_F16_C1] [varchar] (40) NULL, [PID_F16_C2] [varchar] (40) NULL, [PID_F16_C3] [varchar] (40) NULL, [PID_F16_C4] [varchar] (40) NULL, [PID_F16_C5] [varchar] (40) NULL, [PID_F16_C6] [varchar] (40) NULL, [PID_F17_C1] [varchar] (40) NULL, [PID_F17_C2] [varchar] (40) NULL, [PID_F17_C3] [varchar] (40) NULL, [PID_F17_C4] [varchar] (40) NULL, [PID_F17_C5] [varchar] (40) NULL, [PID_F17_C6] [varchar] (40) NULL, [PID_F18_C1] [varchar] (20) NULL, [PID_F18_C2] [varchar] (20) NULL, [PID_F18_C3] [varchar] (20) NULL, [PID_F18_C4] [varchar] (20) NULL, [PID_F18_C5] [varchar] (20) NULL, [PID_F18_C6] [varchar] (20) NULL, [PID_F19_C1] [varchar] (16) NULL, [PID_F20_C1] [varchar] (25) NULL, [PID_F20_C2] [varchar] (20) NULL, [PID_F20_C3] [varchar] (20) NULL, [PID_F21_C1] [varchar] (20) NULL, [PID_F21_C2] [varchar] (20) NULL, [PID_F21_C3] [varchar] (20) NULL, [PID_F21_C4] [varchar] (20) NULL, [PID_F21_C5] [varchar] (20) NULL, [PID_F21_C6] [varchar] (20) NULL, [PID_F22_C1] [varchar] (40) NULL, [PID_F22_C2] [varchar] (40) NULL, [PID_F22_C3] [varchar] (40) NULL, [PID_F22_C4] [varchar] (40) NULL, [PID_F22_C5] [varchar] (40) NULL, [PID_F22_C6] [varchar] (40) NULL, [PID_F23_C1] [varchar] (60) NULL, [PID_F24_C1] [varchar] (10) NULL, [PID_F25_C1] [varchar] (10) NULL, [PID_F26_C1] [varchar] (40) NULL, [PID_F26_C2] [varchar] (40) NULL, [PID_F26_C3] [varchar] (40) NULL, [PID_F26_C4] [varchar] (40) NULL, [PID_F26_C5] [varchar] (40) NULL, [PID_F26_C6] [varchar] (40) NULL, [PID_F27_C1] [varchar] (40) NULL, [PID_F27_C2] [varchar] (40) NULL, [PID_F27_C3] [varchar] (40) NULL, [PID_F27_C4] [varchar] (40) NULL, [PID_F27_C5] [varchar] (40) NULL, [PID_F27_C6] [varchar] (40) NULL, [PID_F28_C1] [varchar] (40) NULL, [PID_F28_C2] [varchar] (40) NULL, [PID_F28_C3] [varchar] (40) NULL, [PID_F28_C4] [varchar] (40) NULL, [PID_F28_C5] [varchar] (40) NULL, [PID_F28_C6] [varchar] (40) NULL, [PID_F29_C1] [varchar] (26) NULL, [PID_F29_C2] [varchar] (26) NULL, [PID_F30_C1] [varchar] (10) NULL ) GO Create UNIQUE Index EH_PID1_MSGIDX on PR1_Segment_PID_A(MessageID,IDX) GO Create Index IDX_PID_F3_C1 on PR1_Segment_PID_A(PID_F3_C1) GO Create Index IDX_PID_F5_C1 on PR1_Segment_PID_A(PID_F5_C1) GO GRANT ALL ON PR1_Segment_PID_A TO PR1_DBAROLE GO
|