Dynamic script to Drop Indices of a specific column I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column).

In my script I have used the normal approach by using the following SQL for known indices (before dropping the column via ALTER TABLE).

IF EXISTS(SELECT * FROM sys.indexes WHERE name = '{IndexName}' AND object_id = OBJECT_ID('{TableName}'))
BEGIN
    DROP INDEX {Table}.{Index};
END


But on some systems of our software I cannot use this approach, because there are some indices that are added manually and I need to identify these indices to drop them also.

I have found a good SQL statement on Stack Overflow to list all indices, columns and tables, but I´m stuck how I can select the names of the indices so that I can pass them to DROP INDEX.

Pseudo-code (something like that):

 declare indiciesToDrop = Select indexName from sql-statement where t.Name = {TableName} and col.Name = {ColumnName};

 DROP INDEX indiciesToDrop;
Since you are planning to DROP a column and the indices that support it, I'll assume that doing so is a product of well-thought plan.  Also, I'll assume that you want to drop ANY indice that use the to-be-deleted column, regardless of how it is used.   If so, you can build your script based upon something like below.  You can put this in a stored procedure and pass the parameters, TableName, ColumnName, and SchemaName in this example.

Now, before you run this or any modified version of it, please be clear that once this script starts, all associated indices plus the column will be dropped, and such decision shouldn't be taken lightly.  

I'm assuming you have already done your homework, but please allow me.  One situation that might become a problem is when the column you want to drop (C) is a part of a multi-column index that supports a popular query.  For example, let's consider the following structure.

CREATE NONCLUSTERED INDEX nci_tableA_ABC ON dbo.A(A,B,C) INCLUDE (D,E);

SELECT D,E FROM dbo.A WHERE A=@? AND B=@? AND C=@?


If this query is very frequently used hence the index is frequently used, dropping this index will severely impact the performance, and the index needs to be manually recreated albeit without the column C.  I'm sure there are other risks to consider.

Also please note that this script is completely void of error trapping or constraint checking.  If the column you are interested has constraint(s), that need to be dropped before the column can be dropped. You can use sys.objects table to construct dynamic SQL.  (and this might help https://stackoverflow.com/questions/13818837/how-to-check-if-default-value-constraint-exist-not-exist)

SET NOCOUNT ON;

IF OBJECT_ID(N'Tempdb..#IndexList', 'U') IS NOT NULL
    DROP TABLE #IndexList;
GO
DECLARE 
    @SchemaName SYSNAME,
    @DropThisIndex SYSNAME,
    @DropThisColumn SYSNAME,
    @CheckThisTable SYSNAME,
    @LoopCounter INT,
    @IndexCount INT,
    @DropColumnSQL NVARCHAR(1000);

SET @SchemaName = N'SchemaName'
SET @CheckThisTable = @SchemaName + '.' + N'TableName';
SET @DropThisColumn = N'ColumnName';
SET @LoopCounter = 1;
SET @DropColumnSQL = 'ALTER TABLE ' + @CheckThisTable + ' DROP COLUMN ' + @DropThisColumn

--First check if the column even exists--

IF EXISTS(SELECT * FROM sys.columns 
            WHERE Name = @DropThisColumn AND Object_ID = Object_ID(@CheckThisTable))

    BEGIN --the column exists in the specified table Construct a temporary table that stores name of index to be dropped--
        SELECT 
            I.name AS IndexName
        INTO #IndexList
        FROM 
            sys.index_columns IC 
            INNER JOIN 
            sys.objects O
        ON  IC.[object_id]= O.[Object_ID]
            INNER JOIN
            sys.indexes I
        ON  IC.[object_id] = I.[object_id] AND
            IC.index_id = I.index_id
            INNER JOIN 
            sys.columns C 
        ON  IC.[object_id] = C.[object_id] AND
            IC.column_id = C.column_id
        WHERE O.[type] NOT IN ('S','IT') AND --we don't want anything to do with System or Internal Table
            IC.[INDEX_ID] &lt;&gt; 1 AND --neither Clustered Index
            IC.[object_id] = OBJECT_id(@CheckThisTable,'U') AND
            C.name = @DropThisColumn;

        --Check how many times to loop through--
        SET @IndexCount = @@ROWCOUNT;
        PRINT CAST(@IndexCount AS NVARCHAR) + N' index(es) on the table [' + @CheckThisTable + N'] for the column [' +  @DropThisColumn + '] found'

        IF @IndexCount &gt; 0 --there is at least one index(es) that are associated with the table and the column in question.  Begin loop--
            BEGIN
                WHILE @LoopCounter &lt; @IndexCount+1
                    BEGIN
                        SELECT TOP(@LoopCounter) 
                            @DropThisIndex = IndexName
                        FROM #IndexList;
                        --Execute Drop Index statement--
                        PRINT N'Loop ' + CONVERT(NVARCHAR,@LoopCounter) + N': Dropping [' + @DropThisIndex + ']'
                        EXEC('DROP INDEX ' + @DropthisIndex + ' ON ' + @CheckThisTable);
                        SET @LoopCounter +=1;
                    END;
                --Dropping Index is done. Let's drop the column now
                PRINT N'Dropping the column [' + @DropThiscolumn + ']'  
                EXEC(@DropColumnSQL);
            END;             
        ELSE --There was no index.  let's just drop the column.
            BEGIN
                PRINT N'Dropping the column [' + @DropThiscolumn + ']'    
                EXEC(@DropColumnSQL);
            END
    END
ELSE --no column found in the specified table.  Are the input corrects?  
    PRINT 'The column [' + @DropThisColumn + '] in the table [' + @CheckThisTable + '] wasn''t found' 
;


Now, if your goal is to drop ONLY the indices that has the to-be-dropped column as the key (left most key that is), then you can modify #IndexList  table like below and filter unwanted row, like Clustered Index, before your get the loop count. 

CREATE TABLE #IndexList
    (IndexName SYSNAME,
    IndexType NVARCHAR(255),
    Index_Key SYSNAME)

INSERT INTO #indexlist
EXEC sys.sp_helpindex N'TableName';