IF (EXISTS (SELECT 1
            FROM sys.indexes i
                     JOIN sys.tables t ON t.object_id = i.object_id
            WHERE t.name = 'T_RECIPE'
              AND i.name = 'UQ_T_RECIPE_SerialNumberDeleted_recipeForm'))
BEGIN
    DROP INDEX [UQ_T_RECIPE_SerialNumberDeleted_recipeForm] ON [AKUZ].[T_RECIPE]
END
GO

IF EXISTS (
    SELECT 1
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.name = 'T_RECIPE'
      AND schema_name(t.schema_id) = 'AKUZ'
      AND c.name = 'year'
)
BEGIN
    ALTER TABLE [AKUZ].[T_RECIPE]
    DROP COLUMN [year];
END
GO

ALTER TABLE [AKUZ].[T_RECIPE]
ADD [Year] INT NULL;
GO

UPDATE [AKUZ].[T_RECIPE]
SET [Year] = YEAR([RecipeDate]);
GO

ALTER TABLE [AKUZ].[T_RECIPE]
ALTER COLUMN [Year] INT NOT NULL;
GO

declare @type_ int = (select top (1) EntityID from vclib.T_ENTITIES where EntName = 'recipe_form' and [schema] = 'akuz')
declare @sql nvarchar(1000)
set @sql = N'
CREATE UNIQUE INDEX [UQ_T_RECIPE_SerialNumberDeleted_recipeForm] ON [AKUZ].[T_RECIPE]
(
[Serial] ASC,
[Number] ASC,
[Year]
)
where Deleted = 0 and MedicalRecordType = ' + cast(@type_ as nvarchar(11))
exec sp_executesql @sql;
GO