IF EXISTS(SELECT 1 FROM SYS.procedures WHERE name = 'sp_AlterDropColumn')
BEGIN
	DROP PROCEDURE dbo.sp_AlterDropColumn
END;
GO
CREATE PROCEDURE dbo.sp_AlterDropColumn

	@TableName NVARCHAR(128) = 'SCHEMA AND TABLENAME', 
	@ColumnName NVARCHAR(100) = 'COLUMN NAME', 
	@Command INT = '0 = DROP 1 = ALTER', 
	@Alter NVARCHAR(50) = 'NVARCHAR(50)'

AS

	DECLARE @ObjID INT = OBJECT_ID(@TableName)
	DECLARE @IXID XML
	DECLARE @DropColumn INT
	
	DECLARE @SqlEX TABLE (ID INT IDENTITY(1,1), [OriginalEx] NVARCHAR(4000), [AlterEx] NVARCHAR(4000), [DropEx] NVARCHAR(4000))

------------------------------------------------------------------------------------------
------ Собираем информацию по индексам указанного поля
------------------------------------------------------------------------------------------
SELECT 
	@IXID = CONCAT(CAST(@IXID AS NVARCHAR(100)) + ', ', '') + CAST(I.index_id AS NVARCHAR(100)),
	@DropColumn = AC.column_id
FROM SYS.indexes I
  JOIN SYS.index_columns IC ON IC.object_id = I.object_id AND IC.index_id = I.index_id
  JOIN SYS.all_columns AC ON AC.object_id = I.object_id AND AC.column_id = IC.column_id
WHERE I.object_id = @ObjID
  AND AC.[name] = @ColumnName

------------------------------------------------------------------------------------------
------ Собираем в массив
------------------------------------------------------------------------------------------

SET @IXID = CAST('<i>' + REPLACE(CAST(@IXID AS NVARCHAR(100)),',','</i><i>') + '</i>' AS XML)

--SELECT @IXID

------------------------------------------------------------------------------------------
------ Проверяем на реальность данного поля и запускаем сборку индекса в строку
------------------------------------------------------------------------------------------

IF @DropColumn IS NOT NULL
BEGIN
	
	INSERT INTO @SqlEX([OriginalEx], [AlterEx], [DropEx])   
	SELECT
		------------------------------------------------------------------------------------------
		------ Оригинальный индекс без изменений
		------------------------------------------------------------------------------------------

		'CREATE ' + 
		CASE 
			WHEN is_primary_key = 1 THEN 'CLUSTERED' 
			WHEN is_primary_key = 0 and is_unique_constraint=0 THEN 'NONCLUSTERED'
			WHEN is_primary_key = 0 and is_unique_constraint=1 THEN 'UNIQUE' END  
		+ ' INDEX ' +
		QUOTENAME(i.name) + ' ON ' +
		QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ' ( '  + 
		STUFF(REPLACE(REPLACE((
		        SELECT 
					QUOTENAME(c.name) 
					+ CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
		        FROM sys.index_columns AS ic
					JOIN sys.columns AS c ON 
						ic.object_id = c.object_id 
						AND ic.column_id = c.column_id
		        WHERE 
					ic.object_id = i.object_id 
					AND ic.index_id = i.index_id
					AND ic.is_included_column = 0
		        ORDER BY ic.key_ordinal
		        FOR XML PATH
		    ), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) '
		+ COALESCE(' INCLUDE ( ' +
		    STUFF(REPLACE(REPLACE((
		        SELECT QUOTENAME(c.name) AS [data()]
				FROM sys.index_columns AS ic
					JOIN sys.columns AS c ON 
						ic.object_id = c.object_id 
						AND ic.column_id = c.column_id
		        WHERE 
					ic.object_id = i.object_id 
					AND ic.index_id = i.index_id 
					AND ic.is_included_column = 1
		        ORDER BY ic.index_column_id
		        FOR XML PATH
		    ), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) ',
		    '') 
		+ ' WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, MAXDOP = 0)' AS 'Original',

		------------------------------------------------------------------------------------------
		------ Индекс без поля
		------------------------------------------------------------------------------------------
		CASE
			WHEN (
				SELECT COUNT(*) 
				FROM sys.index_columns AS ic 
					JOIN sys.columns AS c ON 
						ic.object_id = c.object_id
						AND ic.column_id = c.column_id
				WHERE 
					ic.object_id = i.object_id 
					AND ic.index_id = i.index_id
					AND c.column_id != @DropColumn 
					AND ic.is_included_column = 0) < 1 THEN 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
			ELSE
		'CREATE ' + 
		CASE 
			WHEN is_primary_key=1 THEN 'CLUSTERED' 
			WHEN is_primary_key=0 and is_unique_constraint=0 THEN 'NONCLUSTERED'
			WHEN is_primary_key=0 and is_unique_constraint=1 THEN 'UNIQUE' END  
		+ ' INDEX ' +
		QUOTENAME(i.name) + ' ON ' +
		QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ' ( '  + 
		STUFF(REPLACE(REPLACE((
		        SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
		        FROM sys.index_columns AS ic
					JOIN sys.columns AS c ON 
						ic.object_id = c.object_id 
						AND ic.column_id = c.column_id
		        WHERE 
					ic.object_id = i.object_id 
					AND ic.index_id = i.index_id
					AND ic.is_included_column = 0
					AND c.column_id != @DropColumn
		        ORDER BY ic.key_ordinal
		        FOR XML PATH
		    ), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) '
		+ COALESCE(' INCLUDE ( ' +
		    STUFF(REPLACE(REPLACE((
		        SELECT QUOTENAME(c.name) AS [data()]
				FROM sys.index_columns AS ic
					JOIN sys.columns AS c ON 
						ic.object_id = c.object_id 
						AND ic.column_id = c.column_id
		        WHERE 
					ic.object_id = i.object_id 
					AND ic.index_id = i.index_id 
					AND ic.is_included_column = 1
					AND c.column_id != @DropColumn
		        ORDER BY ic.index_column_id
		        FOR XML PATH
		    ), '<row>', ', '), '</row>', ''), 1, 2, '') + ' ) ',
		    '') 
		+ ' WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, MAXDOP = 0, DROP_EXISTING = ON)' END AS 'Alter', -- DROP_EXISTING дропает существующий с таким именем
		------------------------------------------------------------------------------------------
		------ Удаление индекса
		------------------------------------------------------------------------------------------

		'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) as [Drop]
	FROM sys.tables AS t
		JOIN sys.indexes AS i ON t.object_id = i.object_id
	WHERE 
		t.is_ms_shipped = 0
		AND i.type <> 0
		AND i.[object_id] = @ObjID
		AND i.index_id IN (
			(SELECT x.i.value('.','INT') FROM @IXID.nodes('//i') x(i)));

END
ELSE
BEGIN
	IF EXISTS(
		SELECT 1
		FROM SYS.all_columns AC
		WHERE 
			AC.[object_id] = @ObjID 
			AND AC.[name] = @ColumnName
	)
	BEGIN
		GOTO AlterColumnWithoutIndex;
	END
	ELSE
	PRINT 'COLUMN ' + @ColumnName + ' NOT FOUND'
	RETURN;
END;

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @I INT = (SELECT MAX(ID) FROM @SqlEX)

DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();

------------------------------------------------------------------------------------------
------ Если просто удаление поля, то пересоздаём индексы без учёта оного
------------------------------------------------------------------------------------------

IF @Command = 0
BEGIN TRY
    BEGIN TRANSACTION; 

	WHILE @I > 0
	BEGIN
	
	    SELECT TOP 1 @SQL = [AlterEx] FROM @SqlEX WHERE ID = @I
	
		EXECUTE sp_executesql @SQL
	
	    SET @I = @I - 1
	
	END;

	SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName

	EXECUTE sp_executesql @SQL

	COMMIT TRANSACTION; -- Успешно завершили все операции
	PRINT 'Transaction committed successfully.';

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION; -- Откатываем транзакцию при ошибке

    IF ERROR_NUMBER() != 50000
	BEGIN
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();

		--PRINT 'Global error. Transaction rolled back.'
        PRINT 'Error Message: ' + @ErrorMessage;
        --PRINT 'Severity: ' + CAST(@ErrorSeverity AS VARCHAR(10));
        --PRINT 'State: ' + CAST(@ErrorState AS VARCHAR(10));
		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END

END CATCH;

------------------------------------------------------------------------------------------
------ Если просто замена поля, то удаляем индексы, меняем поле, возвращаем оригинал
------------------------------------------------------------------------------------------

IF @Command = 1
BEGIN TRY
    BEGIN TRANSACTION; 

	WHILE @I > 0
	BEGIN
	
	    SELECT TOP 1 @SQL = [DropEx] FROM @SqlEX WHERE ID = @I
	
		EXECUTE sp_executesql @SQL
	
	    SET @I = @I - 1
	
	END;

	SET @SQL = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @Alter

	EXECUTE sp_executesql @SQL

	SET @I = (SELECT MAX(ID) FROM @SqlEX)

	WHILE @I > 0
	BEGIN
	
	    SELECT TOP 1 @SQL = [OriginalEx] FROM @SqlEX WHERE ID = @I
	
		EXECUTE sp_executesql @SQL
	
	    SET @I = @I - 1
	
	END;

	COMMIT TRANSACTION; -- Успешно завершили все операции
	PRINT 'Transaction committed successfully.';

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION; -- Откатываем транзакцию при ошибке

   IF ERROR_NUMBER() != 50000
	BEGIN
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();

		--PRINT 'Global error. Transaction rolled back.'
        SET @ErrorMessage = 'Error Message: ' + @ErrorMessage;
        --PRINT 'Severity: ' + CAST(@ErrorSeverity AS VARCHAR(10));
        --PRINT 'State: ' + CAST(@ErrorState AS VARCHAR(10));
		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END

END CATCH;

GOTO EndGoTO -- Переходим к последнему шагу, что бы не дублировать команды

--------------------------------------------------------------------------------------------
-------- Если просто замена поля которого нет в индексе
--------------------------------------------------------------------------------------------

AlterColumnWithoutIndex:

BEGIN TRY
    BEGIN TRANSACTION; 

	IF @Command = 0
	BEGIN
		SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName
	END;

	IF @Command = 1

	BEGIN
		SET @SQL = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @Alter
	END;

	EXECUTE sp_executesql @SQL

	COMMIT TRANSACTION; -- Успешно завершили все операции
	PRINT 'Transaction committed successfully.';

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION; -- Откатываем транзакцию при ошибке

   IF ERROR_NUMBER() != 50000
	BEGIN
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();

		--PRINT 'Global error. Transaction rolled back.'
        SET @ErrorMessage = 'Error Message: ' + @ErrorMessage;
        --PRINT 'Severity: ' + CAST(@ErrorSeverity AS VARCHAR(10));
        --PRINT 'State: ' + CAST(@ErrorState AS VARCHAR(10));
		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END

END CATCH;

--------------------------------------------------------------------------------------------
-------- Выходим из шага
--------------------------------------------------------------------------------------------

EndGoTO:

RETURN;