/************************************************************************************/ /* Unified Medical Language System - Microsoft SQL Server database load script */ /* */ /* Metathesaurus Load Script */ /* Use this script on an empty database to create UMLS Metathesaurus tables and */ /* load them with data from RRF files. You only have to specify the @DataFilePath */ /* variable to point to the META folder of your subset. */ /* */ /* February 2011 - By Dr. Nader Elshehabi */ /* dr.nader.elshehabi@gmail.com */ /************************************************************************************/ /****** Declaring variables that will be used in loading data ******/ DECLARE @DataFilePath NVARCHAR(500); DECLARE @AllTables NVARCHAR(500); DECLARE @ChangeTables NVARCHAR(500); DECLARE @CurrentFile NVARCHAR(500); DECLARE @CurrentPosition INT; DECLARE @CurrentTable NVARCHAR(500); DECLARE @statement NVARCHAR(500); /* Change this path to the root folder of your metathesaurus e.g. N'D:\UMLS\2010AA\META' */ SET @DataFilePath = N'TODO: TYPE YOUR META FOLDER PATH HERE' /****** Comma separated list of table names which are mapped to RRF files in the path mentioned above ******/ SET @AllTables = N'ambiglui,ambigsui,mraui,mrcoc,mrcols,mrconso,mrcui,mrdef,mrdoc,mrfiles,mrhier,mrhist,mrmap,mrrank,mrrel,mrsab,mrsat,mrsmap,mrsty,mrxns_eng,mrxnw_eng,mrxw_baq,mrxw_cze,mrxw_dan,mrxw_dut,mrxw_eng,mrxw_fin,mrxw_fre,mrxw_ger,mrxw_heb,mrxw_hun,mrxw_ita,mrxw_jpn,mrxw_kor,mrxw_lav,mrxw_nor,mrxw_por,mrxw_rus,mrxw_scr,mrxw_spa,mrxw_swe' /****** Tables mapped to files under CHANGE subfolder ******/ SET @ChangeTables = N'deletedcui,deletedlui,deletedsui,mergedcui,mergedlui' /****** Creating Tables Schema by DROP and CREATE ******/ /****** DROP tables if exist first ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ambiglui]') AND type in (N'U')) DROP TABLE [dbo].[ambiglui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ambigsui]') AND type in (N'U')) DROP TABLE [dbo].[ambigsui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletedcui]') AND type in (N'U')) DROP TABLE [dbo].[deletedcui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletedlui]') AND type in (N'U')) DROP TABLE [dbo].[deletedlui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletedsui]') AND type in (N'U')) DROP TABLE [dbo].[deletedsui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mergedcui]') AND type in (N'U')) DROP TABLE [dbo].[mergedcui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mergedlui]') AND type in (N'U')) DROP TABLE [dbo].[mergedlui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mraui]') AND type in (N'U')) DROP TABLE [dbo].[mraui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcoc]') AND type in (N'U')) DROP TABLE [dbo].[mrcoc] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcols]') AND type in (N'U')) DROP TABLE [dbo].[mrcols] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND type in (N'U')) DROP TABLE [dbo].[mrconso] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcui]') AND type in (N'U')) DROP TABLE [dbo].[mrcui] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcxt]') AND type in (N'U')) DROP TABLE [dbo].[mrcxt] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrdef]') AND type in (N'U')) DROP TABLE [dbo].[mrdef] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrdoc]') AND type in (N'U')) DROP TABLE [dbo].[mrdoc] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrfiles]') AND type in (N'U')) DROP TABLE [dbo].[mrfiles] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrhier]') AND type in (N'U')) DROP TABLE [dbo].[mrhier] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrhist]') AND type in (N'U')) DROP TABLE [dbo].[mrhist] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrmap]') AND type in (N'U')) DROP TABLE [dbo].[mrmap] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrrank]') AND type in (N'U')) DROP TABLE [dbo].[mrrank] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrrel]') AND type in (N'U')) DROP TABLE [dbo].[mrrel] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsab]') AND type in (N'U')) DROP TABLE [dbo].[mrsab] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsat]') AND type in (N'U')) DROP TABLE [dbo].[mrsat] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsmap]') AND type in (N'U')) DROP TABLE [dbo].[mrsmap] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsty]') AND type in (N'U')) DROP TABLE [dbo].[mrsty] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxns_eng]') AND type in (N'U')) DROP TABLE [dbo].[mrxns_eng] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxnw_eng]') AND type in (N'U')) DROP TABLE [dbo].[mrxnw_eng] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_baq]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_baq] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_cze]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_cze] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_dan]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_dan] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_dut]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_dut] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_eng]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_eng] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_fin]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_fin] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_fre]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_fre] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_ger]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_ger] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_heb]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_heb] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_hun]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_hun] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_ita]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_ita] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_jpn]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_jpn] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_kor]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_kor] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_lav]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_lav] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_nor]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_nor] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_por]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_por] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_rus]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_rus] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_scr]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_scr] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_spa]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_spa] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_swe]') AND type in (N'U')) DROP TABLE [dbo].[mrxw_swe] /****** CREATE tables and indexes ******/ /****** Object: Table [dbo].[mrxw_swe] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_swe]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_swe]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_spa] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_spa]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_spa]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_scr] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_scr]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_scr]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_rus] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_rus]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_rus]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_por] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_por]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_por]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_nor] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_nor]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_nor]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_lav] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_lav]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_lav]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_kor] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_kor]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_kor]( [LAT] [char](3) NOT NULL, [WD] [varchar](500) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_jpn] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_jpn]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_jpn]( [LAT] [char](3) NOT NULL, [WD] [varchar](500) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_ita] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_ita]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_ita]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_hun] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_hun]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_hun]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_heb] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_heb]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_heb]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_ger] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_ger]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_ger]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_fre] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_fre]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_fre]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_fin] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_fin]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_fin]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_eng] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_eng]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_eng]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_dut] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_dut]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_dut]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_dan] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_dan]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_dan]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_cze] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_cze]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_cze]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxw_baq] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxw_baq]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxw_baq]( [LAT] [char](3) NOT NULL, [WD] [varchar](200) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxnw_eng] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxnw_eng]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxnw_eng]( [LAT] [char](3) NOT NULL, [NWD] [varchar](100) NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrxns_eng] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrxns_eng]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrxns_eng]( [LAT] [char](3) NOT NULL, [NSTR] [text] NOT NULL, [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NOT NULL, [SUI] [varchar](10) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrsty] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsty]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrsty]( [CUI] [char](8) NOT NULL, [TUI] [char](4) NOT NULL, [STN] [varchar](100) NOT NULL, [STY] [varchar](50) NOT NULL, [ATUI] [varchar](11) NOT NULL, [CVF] [int] NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrsmap] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsmap]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrsmap]( [MAPSETCUI] [char](8) NOT NULL, [MAPSETSAB] [varchar](20) NOT NULL, [MAPID] [varchar](50) NOT NULL, [MAPSID] [varchar](50) NULL, [FROMEXPR] [text] NOT NULL, [FROMTYPE] [varchar](50) NOT NULL, [REL] [varchar](4) NOT NULL, [RELA] [varchar](100) NULL, [TOEXPR] [text] NULL, [TOTYPE] [varchar](50) NULL, [CVF] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrsat] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsat]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrsat]( [CUI] [char](8) NOT NULL, [LUI] [varchar](10) NULL, [SUI] [varchar](10) NULL, [METAUI] [varchar](50) NULL, [STYPE] [varchar](50) NOT NULL, [CODE] [varchar](50) NULL, [ATUI] [varchar](11) NOT NULL, [SATUI] [varchar](50) NULL, [ATN] [varchar](50) NOT NULL, [SAB] [varchar](20) NOT NULL, [ATV] [text] NULL, [SUPPRESS] [char](1) NOT NULL, [CVF] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrsab] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrsab]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrsab]( [VCUI] [char](8) NULL, [RCUI] [char](8) NULL, [VSAB] [varchar](20) NOT NULL, [RSAB] [varchar](20) NOT NULL, [SON] [text] NOT NULL, [SF] [varchar](20) NOT NULL, [SVER] [varchar](20) NULL, [VSTART] [char](8) NULL, [VEND] [char](8) NULL, [IMETA] [varchar](10) NOT NULL, [RMETA] [varchar](10) NULL, [SLC] [text] NULL, [SCC] [text] NULL, [SRL] [int] NOT NULL, [TFR] [int] NULL, [CFR] [int] NULL, [CXTY] [varchar](50) NULL, [TTYL] [varchar](300) NULL, [ATNL] [text] NULL, [LAT] [char](3) NULL, [CENC] [varchar](20) NOT NULL, [CURVER] [char](1) NOT NULL, [SABIN] [char](1) NOT NULL, [SSN] [text] NOT NULL, [SCIT] [text] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrrel] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrrel]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrrel]( [CUI1] [char](8) NOT NULL, [AUI1] [varchar](9) NULL, [STYPE1] [varchar](50) NOT NULL, [REL] [varchar](4) NOT NULL, [CUI2] [char](8) NOT NULL, [AUI2] [varchar](9) NULL, [STYPE2] [varchar](50) NOT NULL, [RELA] [varchar](100) NULL, [RUI] [varchar](10) NOT NULL, [SRUI] [varchar](50) NULL, [SAB] [varchar](20) NOT NULL, [SL] [varchar](20) NOT NULL, [RG] [varchar](10) NULL, [DIR] [varchar](1) NULL, [SUPPRESS] [char](1) NOT NULL, [CVF] [int] NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrrank] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrrank]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrrank]( [RANK] [int] NOT NULL, [SAB] [varchar](20) NOT NULL, [TTY] [varchar](20) NOT NULL, [SUPPRESS] [char](1) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrmap] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrmap]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrmap]( [MAPSETCUI] [char](8) NOT NULL, [MAPSETSAB] [varchar](20) NOT NULL, [MAPSUBSETID] [varchar](10) NULL, [MAPRANK] [int] NULL, [MAPID] [varchar](50) NOT NULL, [MAPSID] [varchar](50) NULL, [FROMID] [varchar](50) NOT NULL, [FROMSID] [varchar](50) NULL, [FROMEXPR] [text] NOT NULL, [FROMTYPE] [varchar](50) NOT NULL, [FROMRULE] [text] NULL, [FROMRES] [text] NULL, [REL] [varchar](4) NOT NULL, [RELA] [varchar](100) NULL, [TOID] [varchar](50) NULL, [TOSID] [varchar](50) NULL, [TOEXPR] [text] NULL, [TOTYPE] [varchar](50) NULL, [TORULE] [text] NULL, [TORES] [text] NULL, [MAPRULE] [text] NULL, [MAPRES] [text] NULL, [MAPTYPE] [varchar](50) NULL, [MAPATN] [varchar](20) NULL, [MAPATV] [text] NULL, [CVF] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrhist] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrhist]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrhist]( [CUI] [char](8) NULL, [SOURCEUI] [varchar](50) NULL, [SAB] [varchar](20) NULL, [SVER] [varchar](20) NULL, [CHANGETYPE] [text] NULL, [CHANGEKEY] [text] NULL, [CHANGEVAL] [text] NULL, [REASON] [text] NULL, [CVF] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrhier] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrhier]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrhier]( [CUI] [char](8) NOT NULL, [AUI] [varchar](9) NOT NULL, [CXN] [int] NOT NULL, [PAUI] [varchar](10) NULL, [SAB] [varchar](20) NOT NULL, [RELA] [varchar](100) NULL, [PTR] [text] NULL, [HCD] [varchar](50) NULL, [CVF] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrhier]') AND name = N'X_MRHIER_AUI') CREATE NONCLUSTERED INDEX [X_MRHIER_AUI] ON [dbo].[mrhier] ( [AUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrhier]') AND name = N'X_MRHIER_CUI') CREATE NONCLUSTERED INDEX [X_MRHIER_CUI] ON [dbo].[mrhier] ( [CUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrhier]') AND name = N'X_MRHIER_SAB') CREATE NONCLUSTERED INDEX [X_MRHIER_SAB] ON [dbo].[mrhier] ( [SAB] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] /****** Object: Table [dbo].[mrfiles] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrfiles]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrfiles]( [FIL] [varchar](50) NULL, [DES] [varchar](200) NULL, [FMT] [text] NULL, [CLS] [int] NULL, [RWS] [int] NULL, [BTS] [bigint] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrdoc] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrdoc]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrdoc]( [DOCKEY] [varchar](50) NOT NULL, [VALUE_] [varchar](200) NULL, [TYPE] [varchar](50) NOT NULL, [EXPL] [text] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrdef] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrdef]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrdef]( [CUI] [char](8) NOT NULL, [AUI] [varchar](9) NOT NULL, [ATUI] [varchar](11) NOT NULL, [SATUI] [varchar](50) NULL, [SAB] [varchar](20) NOT NULL, [DEF] [text] NOT NULL, [SUPPRESS] [char](1) NOT NULL, [CVF] [int] NULL, PRIMARY KEY CLUSTERED ( [ATUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrdef]') AND name = N'primary key') CREATE UNIQUE NONCLUSTERED INDEX [primary key] ON [dbo].[mrdef] ( [ATUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrdef]') AND name = N'X_MRDEF_AUI') CREATE NONCLUSTERED INDEX [X_MRDEF_AUI] ON [dbo].[mrdef] ( [AUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrdef]') AND name = N'X_MRDEF_CUI') CREATE NONCLUSTERED INDEX [X_MRDEF_CUI] ON [dbo].[mrdef] ( [CUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrdef]') AND name = N'X_MRDEF_SAB') CREATE NONCLUSTERED INDEX [X_MRDEF_SAB] ON [dbo].[mrdef] ( [SAB] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] /****** Object: Table [dbo].[mrcxt] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcxt]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrcxt]( [CUI] [char](8) NULL, [SUI] [varchar](10) NULL, [AUI] [varchar](9) NULL, [SAB] [varchar](20) NULL, [CODE] [varchar](50) NULL, [CXN] [int] NULL, [CXL] [char](3) NULL, [RANK] [int] NULL, [CXS] [text] NULL, [CUI2] [char](8) NULL, [AUI2] [varchar](9) NULL, [HCD] [varchar](50) NULL, [RELA] [varchar](100) NULL, [XC] [varchar](1) NULL, [CVF] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcxt]') AND name = N'X_MRCXT_AUI') CREATE NONCLUSTERED INDEX [X_MRCXT_AUI] ON [dbo].[mrcxt] ( [AUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcxt]') AND name = N'X_MRCXT_CUI') CREATE NONCLUSTERED INDEX [X_MRCXT_CUI] ON [dbo].[mrcxt] ( [CUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcxt]') AND name = N'X_MRCXT_SAB') CREATE NONCLUSTERED INDEX [X_MRCXT_SAB] ON [dbo].[mrcxt] ( [SAB] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] /****** Object: Table [dbo].[mrcui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrcui]( [CUI1] [char](8) NOT NULL, [VER] [varchar](10) NOT NULL, [REL] [varchar](4) NOT NULL, [RELA] [varchar](100) NULL, [MAPREASON] [text] NULL, [CUI2] [char](8) NULL, [MAPIN] [char](1) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrconso] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrconso]( [CUI] [char](8) NOT NULL, [LAT] [char](3) NOT NULL, [TS] [char](1) NOT NULL, [LUI] [varchar](10) NOT NULL, [STT] [varchar](3) NOT NULL, [SUI] [varchar](10) NOT NULL, [ISPREF] [char](1) NOT NULL, [AUI] [varchar](9) NOT NULL, [SAUI] [varchar](50) NULL, [SCUI] [varchar](50) NULL, [SDUI] [varchar](50) NULL, [SAB] [varchar](20) NOT NULL, [TTY] [varchar](20) NOT NULL, [CODE] [varchar](50) NOT NULL, [STR] [text] NOT NULL, [SRL] [int] NOT NULL, [SUPPRESS] [char](1) NOT NULL, [CVF] [int] NULL, PRIMARY KEY CLUSTERED ( [AUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'primary key') CREATE UNIQUE NONCLUSTERED INDEX [primary key] ON [dbo].[mrconso] ( [AUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'X_MRCONSO_CODE') CREATE NONCLUSTERED INDEX [X_MRCONSO_CODE] ON [dbo].[mrconso] ( [CODE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'X_MRCONSO_CUI') CREATE NONCLUSTERED INDEX [X_MRCONSO_CUI] ON [dbo].[mrconso] ( [CUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'X_MRCONSO_LUI') CREATE NONCLUSTERED INDEX [X_MRCONSO_LUI] ON [dbo].[mrconso] ( [LUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'X_MRCONSO_SAB_TTY') CREATE NONCLUSTERED INDEX [X_MRCONSO_SAB_TTY] ON [dbo].[mrconso] ( [SAB] ASC, [TTY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'X_MRCONSO_SCUI') CREATE NONCLUSTERED INDEX [X_MRCONSO_SCUI] ON [dbo].[mrconso] ( [SCUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'X_MRCONSO_SDUI') CREATE NONCLUSTERED INDEX [X_MRCONSO_SDUI] ON [dbo].[mrconso] ( [SDUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrconso]') AND name = N'X_MRCONSO_SUI') CREATE NONCLUSTERED INDEX [X_MRCONSO_SUI] ON [dbo].[mrconso] ( [SUI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] /****** Object: Table [dbo].[mrcols] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcols]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrcols]( [COL] [varchar](20) NULL, [DES] [varchar](200) NULL, [REF] [varchar](20) NULL, [MIN_] [int] NULL, [AV] [decimal](5, 2) NULL, [MAX_] [int] NULL, [FIL] [varchar](50) NULL, [DTY] [varchar](20) NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mrcoc] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mrcoc]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mrcoc]( [CUI1] [char](8) NOT NULL, [AUI1] [varchar](9) NOT NULL, [CUI2] [char](8) NULL, [AUI2] [varchar](9) NULL, [SAB] [varchar](20) NOT NULL, [COT] [varchar](3) NOT NULL, [COF] [int] NULL, [COA] [text] NULL, [CVF] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcoc]') AND name = N'X_MRCOC_AUI1') CREATE NONCLUSTERED INDEX [X_MRCOC_AUI1] ON [dbo].[mrcoc] ( [AUI1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcoc]') AND name = N'X_MRCOC_AUI2') CREATE NONCLUSTERED INDEX [X_MRCOC_AUI2] ON [dbo].[mrcoc] ( [AUI2] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcoc]') AND name = N'X_MRCOC_CUI1') CREATE NONCLUSTERED INDEX [X_MRCOC_CUI1] ON [dbo].[mrcoc] ( [CUI1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcoc]') AND name = N'X_MRCOC_CUI2') CREATE NONCLUSTERED INDEX [X_MRCOC_CUI2] ON [dbo].[mrcoc] ( [CUI2] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[mrcoc]') AND name = N'X_MRCOC_SAB') CREATE NONCLUSTERED INDEX [X_MRCOC_SAB] ON [dbo].[mrcoc] ( [SAB] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] /****** Object: Table [dbo].[mraui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mraui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mraui]( [AUI1] [varchar](9) NOT NULL, [CUI1] [char](8) NOT NULL, [VER] [varchar](10) NOT NULL, [REL] [varchar](4) NULL, [RELA] [varchar](100) NULL, [MAPREASON] [text] NOT NULL, [AUI2] [varchar](9) NOT NULL, [CUI2] [char](8) NOT NULL, [MAPIN] [char](1) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mergedlui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mergedlui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mergedlui]( [PLUI] [varchar](10) NULL, [LUI] [varchar](10) NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[mergedcui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mergedcui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[mergedcui]( [PCUI] [char](8) NOT NULL, [CUI] [char](8) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[deletedsui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletedsui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[deletedsui]( [PSUI] [varchar](10) NOT NULL, [LAT] [char](3) NOT NULL, [PSTR] [text] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[deletedlui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletedlui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[deletedlui]( [PLUI] [varchar](10) NOT NULL, [PSTR] [text] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[deletedcui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[deletedcui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[deletedcui]( [PCUI] [char](8) NOT NULL, [PSTR] [text] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[ambigsui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ambigsui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ambigsui]( [SUI] [varchar](10) NOT NULL, [CUI] [char](8) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Object: Table [dbo].[ambiglui] ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ambiglui]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ambiglui]( [LUI] [varchar](10) NOT NULL, [CUI] [char](8) NOT NULL ) ON [PRIMARY] END SET ANSI_PADDING OFF /****** Inserting data into tables ******/ if right(rtrim(@AllTables),1) <> ',' set @AllTables = @AllTables + ',' set @CurrentPosition = patindex('%,%' , @AllTables) while @CurrentPosition <> 0 begin set @CurrentTable = left(@AllTables, @CurrentPosition - 1); set @AllTables = stuff(@AllTables, 1, @CurrentPosition, ''); set @CurrentPosition = patindex('%,%' , @AllTables); SET @CurrentFile = @DataFilePath + N'\' + @CurrentTable + N'.rrf'; SET @statement = N' BULK INSERT [' + @CurrentTable + N'] FROM ''' + @CurrentFile + N''' WITH (FIELDTERMINATOR =''|'', ROWTERMINATOR = ''|\n'') '; PRINT('Executing command ' + @statement); EXEC dbo.sp_executesql @statement; END /****** Inserting data into change tables ******/ if right(rtrim(@ChangeTables),1) <> ',' set @ChangeTables = @ChangeTables + ',' set @CurrentPosition = patindex('%,%' , @ChangeTables) while @CurrentPosition <> 0 begin set @CurrentTable = left(@ChangeTables, @CurrentPosition - 1); set @ChangeTables = stuff(@ChangeTables, 1, @CurrentPosition, ''); set @CurrentPosition = patindex('%,%' , @ChangeTables); SET @CurrentFile = @DataFilePath + N'\change\' + @CurrentTable + N'.rrf'; SET @statement = N' BULK INSERT [' + @CurrentTable + N'] FROM ''' + @CurrentFile + N''' WITH (FIELDTERMINATOR =''|'', ROWTERMINATOR = ''|\n'') '; PRINT('Executing command ' + @statement); EXEC dbo.sp_executesql @statement; END