Get name of index for a given table and column with T-SQL

Question

I want to convert some databases from Varchar to nVarchar because of switch to unicode. For that I have begin to write a small Delphi application to generate the needed SQL-script.

Now to alter column from VARCHAR to NVARCHAR I must first drop index if it exists. So my question is how can I get the name of index from any given table and column ?

duplicate!. look at this: http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db — FLICKER, Apr 01 '16 at 08:28

score 2 · Accepted Answer · answered Apr 01 '16 at 08:30

Try this query

SELECT TableName = t.NAME
    ,IndexName = ind.NAME
    ,IndexId = ind.index_id
    ,ColumnId = ic.index_column_id
    ,ColumnName = col.NAME
    ,ind.*
    ,ic.*
    ,col.*
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id
    AND ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id
    AND ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE ind.is_primary_key = 0
    AND ind.is_unique = 0
    AND ind.is_unique_constraint = 0
    AND t.is_ms_shipped = 0
    AND t.name=<Your TableName>
ORDER BY t.NAME
    ,ind.NAME
    ,ind.index_id
    ,ic.index_column_id

Great :) I test at once – Roland Bengtsson Apr 01 '16 at 08:57 — Roland Bengtsson, Apr 01 '16 at 08:57

Get name of index for a given table and column with T-SQL

1 Answers1