IF EXISTS(SELECT r.PurposeCode
FROM REservations r (nolock)
left outer join PURPOSECODES p (nolock) on r.PURPOSECODE = p.PurposeCodeNumber
where r.PURPOSECODE IS Not NUll and p.PurposeCodeNumber IS Null)
	UPDATE Reservations
	Set PURPOSECODE = null
	FROM REservations r (nolock)
	left outer join PURPOSECODES p (nolock) on r.PURPOSECODE = p.PurposeCodeNumber
	where r.PURPOSECODE IS Not NUll and p.PurposeCodeNumber IS Null

 IF (SELECT SysColumns.xtype
     FROM SysObjects WITH (NOLOCK) 
     INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
     AND SysColumns.Name = 'PurposeCode'
     WHERE SysObjects.Name = 'Reservations') = 52 
		ALTER TABLE Reservations ALTER COLUMN PURPOSECODE INT

IF NOT EXISTS (
SELECT *
  FROM SysObjects WITH (NOLOCK) 
  WHERE Name = 'FK_RESERVATIONS_PurposeCodes' AND xtype = 'F')
 BEGIN

	ALTER TABLE [dbo].[RESERVATIONS]  WITH CHECK ADD  CONSTRAINT [FK_RESERVATIONS_PurposeCodes] FOREIGN KEY([PURPOSECODE])
	REFERENCES [dbo].[PURPOSECODES] ([PurposeCodeNumber])


	ALTER TABLE [dbo].[RESERVATIONS] CHECK CONSTRAINT [FK_RESERVATIONS_PurposeCodes]
END
    