Scenario
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’
Solution
1. Launch SSMS and connect to your database
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
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article