List all index columns (including filtered ones) in SQL Server -

how index columns including filtered columns?

if object_id('dbo.ttestindex') not null     drop table dbo.ttestindex;  create table dbo.ttestindex (a int, b int, c int);  create index x_ttestindex on dbo.ttestindex (a) include (b) c > 0;  select i.name, i.filter_definition, c.name, ic.is_included_column sys.indexes inner join sys.index_columns ic on i.object_id = ic.object_id , i.index_id = ic.index_id left join sys.columns c on ic.object_id = c.object_id , ic.column_id = c.column_id i.object_id = object_id('dbo.ttestindex');  if object_id('dbo.ttestindex') not null     drop table dbo.ttestindex; 

this example gives 2 rows instead of three.

index_name   | filter_definition | column_name | is_included_column -------------+-------------------+-------------+------------------- x_ttestindex | ([c]>(0))         |           | 0 x_ttestindex | ([c]>(0))         | b           | 1 

you can use catalog view sys.sql_expression_dependencies find out columns on filter relies:

select  i.name, i.filter_definition, c.name, is_included_column = 0    sys.indexes         inner join sys.sql_expression_dependencies d             on d.referencing_id = i.[object_id]             , d.referencing_minor_id = i.index_id             , d.referencing_class_desc = 'index'         inner join sys.columns c             on c.[object_id] = d.referenced_id             , c.column_id = d.referenced_minor_id   i.[object_id] = object_id(n'dbo.ttestindex'); 

you can union original query columns.