How to check if a table has columnstore index?

Question

I need to make some mechanism that checks if a table X is columnstored or not and if it is not to convert it. I am aware that to convert a table X for example to clustered columnstore index we can use the following code:

CREATE CLUSTERED COLUMNSTORE INDEX MyColumnStoreIndex
ON Table_X

I would like to make a query that checks if a table contains columnstore index. Thanks in advance.

The documentation about indexes indicates quite clearly how to find this information. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql — Sean Lange, Mar 12 '18 at 14:43

score 7 · Accepted Answer · answered Mar 12 '18 at 14:45

7

You can query sys.indexes and check the type and type_desc columns. According to the documentation values 5 and 6 hold information whether an index is clustered or nonclustered columnstore:

select *
from sys.indexes 
where type in (5, 6)

answered Mar 12 '18 at 14:45

Rigerta

3,959
15
26

1

That was really helpful. Thank you :) – alexithymia Mar 12 '18 at 15:42

score 6 · Answer 2 · answered Mar 12 '18 at 15:19

you can query the sys.indexes as mentioned in Rigerta's answer.
To also get the tablename that belongs to found indexes you can do following query

select i.name as indexname, 
       t.name as tablename
from   sys.indexes i
  join sys.tables t on i.object_id = t.object_id
where  i.type in (5, 6)

How to check if a table has columnstore index?

2 Answers2