IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_ProductNumber'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
	IF NOT EXISTS(
		 SELECT SysColumns.Name 
		 FROM SysObjects WITH (NOLOCK) 
         INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
         AND SysColumns.Name = 'ProductNumbers'
    WHERE SysObjects.Name = 'Templates'
	)
	BEGIN
		ALTER TABLE dbo.Templates ALTER COLUMN FK_ProductNumber VARCHAR(500)

		EXEC sp_rename 'dbo.Templates.FK_ProductNumber', 'ProductNumbers', 'COLUMN'
	END ELSE
		ALTER TABLE dbo.Templates DROP Column FK_ProductNumber
END
GO

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ReservationStatus'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
  DECLARE @NAME VARCHAR(100)
  DECLARE @SQL VARCHAR(max)
  SELECT @Name = c.name
  FROM sys.default_constraints c
  inner JOIN sys.tables t ON t.object_id = c.parent_object_id
  inner join sys.columns sc ON c.parent_column_id = sc.column_id and sc.object_id = t.object_id
  WHERE t.name ='templates' and sc.name = 'ReservationStatus'

  SET @NAME = ISNULL(@Name, '')
  IF @Name <> ''
  BEGIN
    SET @SQL =  'ALTER TABLE dbo.Templates DROP ' + @NAME
    EXEC(@SQL)
  END

  IF NOT EXISTS(
		 SELECT SysColumns.Name 
		 FROM SysObjects WITH (NOLOCK) 
         INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
         AND SysColumns.Name = 'ReservationStatuses'
    WHERE SysObjects.Name = 'Templates'
  )
  BEGIN

    ALTER TABLE dbo.Templates ALTER COLUMN ReservationStatus VARCHAR(500)
    EXEC sp_rename 'dbo.Templates.ReservationStatus', 'ReservationStatuses', 'COLUMN'
  END ELSE
	ALTER TABLE dbo.Templates DROP Column ReservationStatus
END
GO


IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ProductNumbers'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
	ALTER TABLE Templates ADD ProductNumbers VARCHAR(500) NULL
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ReservationStatuses'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
	ALTER TABLE Templates ADD ReservationStatuses VARCHAR(500) NULL
END