Check that index file exists

Question

Hi,

I have created an index on my table like this:

CREATE INDEX index_typ_poplatky
    ON Auta (typ DESC, poplatok_denny DESC, poplatok_km DESC);

How to I check that the index file exists?

Answer 1

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[table name]') AND name = N'index name')

Answer 2

To check when connected as the schema owner:

select index_name from user_indexes
where index_name = 'INDEX_TYP_POLATYKY';

or

select index_name from user_indexes
where table_name = 'AUTA';

Note that the index name and table name are stored in uppercase.

You can also select from USER_IND_COLUMNS to find out the columns that are indexed:

select column_name
from user_ind_columns
where index_name = 'INDEX_TYP_POLATYKY'
order by column_position;