if exists
(select * from sys.indexes i where i.object_id = object_id('PHARM.T_BILL_OF_GOODS', 'U') and i.name = 'IX_T_BILL_OF_GOODS_MedicationForms') 
	DROP INDEX IX_T_BILL_OF_GOODS_MedicationForms ON PHARM.T_BILL_OF_GOODS;
GO
if exists
(select * from sys.indexes i where i.object_id = object_id('PHARM.T_BILL_OF_GOODS', 'U') and i.name = 'IX_T_BILL_OF_GOODS_Active') 
	DROP INDEX IX_T_BILL_OF_GOODS_Active ON PHARM.T_BILL_OF_GOODS;
GO
if exists
(select * from sys.indexes i where i.object_id = object_id('PHARM.T_BILL_OF_GOODS', 'U') and i.name = 'IX_T_BILL_OF_GOODS_ExportCode') 
	DROP INDEX IX_T_BILL_OF_GOODS_ExportCode ON PHARM.T_BILL_OF_GOODS;
GO

ALTER TABLE [PHARM].[T_BILL_OF_GOODS] DROP CONSTRAINT [FK_T_MEDICATION_FORMS_T_BILL_OF_GOODS_MedicationForms]
GO
ALTER TABLE [PHARM].[T_MEDICATION_FORMS] DROP CONSTRAINT [PK_T_MEDICATION_FORMS] WITH ( ONLINE = OFF )
GO

ALTER TABLE [PHARM].[T_MEDICATION_FORMS] ADD [MedicationFormsID] uniqueidentifier NULL
GO
UPDATE [PHARM].[T_MEDICATION_FORMS] SET [MedicationFormsID] = newid()
GO
ALTER TABLE [PHARM].[T_MEDICATION_FORMS] ALTER COLUMN [MedicationFormsID] uniqueidentifier NOT NULL
GO

ALTER TABLE [PHARM].[T_MEDICATION_FORMS] ADD CONSTRAINT [PK_T_MEDICATION_FORMS]
PRIMARY KEY CLUSTERED ([MedicationFormsID]) ON [PRIMARY]
GO

--------T_BILL_OF_GOODS
ALTER TABLE PHARM.T_BILL_OF_GOODS add oldMf int
GO

update  PHARM.T_BILL_OF_GOODS set oldMf = MedicationForms
GO

ALTER TABLE PHARM.T_BILL_OF_GOODS DROP COLUMN MedicationForms
GO

ALTER TABLE [PHARM].[T_BILL_OF_GOODS] ADD [MedicationForms] uniqueidentifier NULL
GO

ALTER TABLE [PHARM].[T_BILL_OF_GOODS] ADD CONSTRAINT [FK_T_MEDICATION_FORMS_T_BILL_OF_GOODS_MedicationForms]
FOREIGN KEY([MedicationForms]) REFERENCES [PHARM].[T_MEDICATION_FORMS] ([MedicationFormsID])
GO

update bg
set bg.MedicationForms = mf.[MedicationFormsID]
from PHARM.T_BILL_OF_GOODS bg 
join [PHARM].[T_MEDICATION_FORMS] mf on mf.Code = bg.oldMf

ALTER TABLE PHARM.T_BILL_OF_GOODS DROP COLUMN oldMf

---------------------------------------MEDICAMENT_PRODUCER
ALTER TABLE [PHARM].[T_BILL_OF_GOODS] DROP CONSTRAINT [FK_T_MEDICAMENT_PRODUCER_T_BILL_OF_GOODS_MedicamentProducer]
GO
ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] DROP CONSTRAINT [FK_T_MEDICAMENT_PRODUCER_T_MEDICAMENT_PRODUCER_COUNTRY_LINK_MedicamentProducer]
GO
ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER] DROP CONSTRAINT [PK_T_MEDICAMENT_PRODUCER] WITH ( ONLINE = OFF )
GO



ALTER TABLE [PHARM].T_MEDICAMENT_PRODUCER ADD oldId int
GO
update [PHARM].T_MEDICAMENT_PRODUCER set oldId = MedicamentProducerID
GO
ALTER TABLE [PHARM].T_MEDICAMENT_PRODUCER DROP COLUMN MedicamentProducerID
GO
ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER] ADD [MedicamentProducerID] uniqueidentifier NULL
GO
UPDATE [PHARM].[T_MEDICAMENT_PRODUCER] SET [MedicamentProducerID] = newid()
GO
ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER] ALTER COLUMN [MedicamentProducerID] uniqueidentifier NOT NULL
GO

ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER] ADD CONSTRAINT [PK_T_MEDICAMENT_PRODUCER]
PRIMARY KEY CLUSTERED ([MedicamentProducerID]) ON [PRIMARY]
GO

--------------[T_BILL_OF_GOODS]
ALTER TABLE PHARM.T_BILL_OF_GOODS add oldMp int
GO

update  PHARM.T_BILL_OF_GOODS set oldMp = MedicamentProducer
GO

ALTER TABLE PHARM.T_BILL_OF_GOODS DROP COLUMN MedicamentProducer
GO

ALTER TABLE [PHARM].[T_BILL_OF_GOODS] ADD [MedicamentProducer] uniqueidentifier NULL
GO

update bg
set bg.MedicamentProducer = mf.[MedicamentProducerID]
from [PHARM].[T_BILL_OF_GOODS] bg 
join [PHARM].[T_MEDICAMENT_PRODUCER] mf on mf.oldId = bg.oldMp


ALTER TABLE [PHARM].[T_BILL_OF_GOODS] ALTER COLUMN [MedicamentProducer] uniqueidentifier NOT NULL
GO

ALTER TABLE [PHARM].[T_BILL_OF_GOODS] ADD CONSTRAINT [FK_T_MEDICAMENT_PRODUCER_T_BILL_OF_GOODS_MedicamentProducer]
FOREIGN KEY([MedicamentProducer]) REFERENCES [PHARM].[T_MEDICAMENT_PRODUCER] ([MedicamentProducerID])
GO

ALTER TABLE PHARM.T_BILL_OF_GOODS DROP COLUMN oldMp

-------------[T_MEDICAMENT_PRODUCER_COUNTRY_LINK]
ALTER TABLE PHARM.[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] add oldMp int
GO

update  PHARM.[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] set oldMp = MedicamentProducer
GO

ALTER TABLE PHARM.[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] DROP COLUMN MedicamentProducer
GO

ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] ADD [MedicamentProducer] uniqueidentifier NULL
GO

update bg
set bg.MedicamentProducer = mf.[MedicamentProducerID]
from [PHARM].[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] bg 
join [PHARM].[T_MEDICAMENT_PRODUCER] mf on mf.oldId = bg.oldMp


ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] ALTER COLUMN [MedicamentProducer] uniqueidentifier NOT NULL
GO

ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] ADD CONSTRAINT [FK_T_MEDICAMENT_PRODUCER_T_MEDICAMENT_PRODUCER_COUNTRY_LINK_MedicamentProducer]
FOREIGN KEY([MedicamentProducer]) REFERENCES [PHARM].[T_MEDICAMENT_PRODUCER] ([MedicamentProducerID])
GO

ALTER TABLE PHARM.[T_MEDICAMENT_PRODUCER_COUNTRY_LINK] DROP COLUMN oldMp
GO
ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER] ADD [Code] int NULL
GO
UPDATE [PHARM].[T_MEDICAMENT_PRODUCER] SET [Code] = oldId
GO
ALTER TABLE [PHARM].[T_MEDICAMENT_PRODUCER] ALTER COLUMN [Code] int NOT NULL
GO

ALTER TABLE PHARM.[T_MEDICAMENT_PRODUCER] DROP COLUMN oldId



CREATE NONCLUSTERED INDEX [IX_T_BILL_OF_GOODS_MedicationForms] ON [PHARM].[T_BILL_OF_GOODS] ([MedicationForms] ASC,[PackQuantity] ASC ) INCLUDE([CTrn],[OtherID],[Active],[MedicamentProducer],[IsVital],[FederalID],[DoseView],[Rate],[ExportCode],[EAN],[RegCode]) WITH (FILLFACTOR=90);
GO