Find all Filtered Indexes in SQL Server Database

To find all filtered indexes in all user tables in a SQL Server database, use the following query:

SELECT DB_NAME() AS Database_Name
, AS Schema_Name
, AS Table_Name
, o.type_desc AS 'Table_Type'
, AS Filtered_Index_Name
, i.filter_definition
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.has_filter = 1 AND o.type = 'U'
ORDER BY Table_Name

To test the query, we will create a filtered index in AdventureWorks database, Production.ProductInventory table:

CREATE NONCLUSTERED INDEX IX_ProductInventory_Quantity_Filtered
ON Production.ProductInventory(Quantity) WHERE Quantity < 100

When we run the first query above, we can see the index we created, and the definition of the index: