When upgrade database in QNE Optimum prompted “The index ‘XXX’ is dependent on column ‘Debit’. ALTER TABLE ALTER COLUMN Debit failed because one or more objects access this column.”

In this example, my index is ‘IX_AIDBA_52_20190403’ and my column is ‘Debit’

https://support.qne.com.my/a/solutions/articles/81000385373 

2. First, we need to find out the table name of the index

3. Click “New Query” button

4. Copy and paste below query

select i.[name] as index_name,

    substring(column_names, 1, len(column_names)-1) as [columns],

    case when i.[type] = 1 then 'Clustered index'

        when i.[type] = 2 then 'Nonclustered unique index'

        when i.[type] = 3 then 'XML index'

        when i.[type] = 4 then 'Spatial index'

        when i.[type] = 5 then 'Clustered columnstore index'

        when i.[type] = 6 then 'Nonclustered columnstore index'

        when i.[type] = 7 then 'Nonclustered hash index'

        end as index_type,

    case when i.is_unique = 1 then 'Unique'

        else 'Not unique' end as [unique],

    schema_name(t.schema_id) + '.' + t.[name] as table_view, 

    case when t.[type] = 'U' then 'Table'

        when t.[type] = 'V' then 'View'

        end as [object_type]

from sys.objects t

    inner join sys.indexes i

        on t.object_id = i.object_id

    cross apply (select col.[name] + ', '

                    from sys.index_columns ic

                        inner join sys.columns col

                            on ic.object_id = col.object_id

                            and ic.column_id = col.column_id

                    where ic.object_id = t.object_id

                        and ic.index_id = i.index_id

                            order by col.column_id

                            for xml path ('') ) D (column_names)

where t.is_ms_shipped <> 1

and index_id > 0

and i.[name] = 'MY_INDEX_NAME'

order by i.[name]

5. Replace 'MY_INDEX_NAME' with your index name

6. For my example, I will replace 'MY_INDEX_NAME' with 'IX_AIDBA_52_20190403'

7. Click “Execute” button

8. Result will show you the table name for the index

9. For my example, the table name is “dbo.GLTransactions”

10. Now that we have the index’s table name, then we can remove the index

11. Click “New Query” button

12. Copy and paste below query

DROP INDEX TABLE_NAME.INDEX_NAME

13. Replace “TABLE_NAME” with your table name and “INDEX_NAME” with your index

14. For my example, the query will be

DROP INDEX dbo.GLTransactions.IX_AIDBA_52_20190403

15. Click “Execute” button and the index will be removed

16. Now you can continue to upgrade your database

** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software