Tag Archives: Find All Filtered Indexes

How to find all the filtered indexes or all the tables having filtered indexes in Sql Server?

We can write a query like below to get the name of all the filtered indexes or all the tables having filtered indexes in Sql Server:

SELECT DISTINCT T.Name 'Table Name',
  I.Name 'Filtered Index Name',
  I.Filter_Definition 'Filter Definition'
FROM sys.indexes I		
      INNER JOIN sys.tables T 
        ON I.object_id = T.object_id 
WHERE I.has_filter = 1
ORDER BY T.Name, I.Name

Let us see this with an example:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create a Table with a primary key column having 
a Clustered index, a Non-Clustered Filtered Index and 
a Non-Clustered Index without any filter.*/
CREATE TABLE dbo.Orders (
  OrderId int IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL,
  CustomerId int NOT NULL ,
  CreationDT DATETIME NOT NULL)
GO
CREATE NONCLUSTERED INDEX IX_FilteredIndex
	ON dbo.Orders(CreationDT) WHERE OrderId > 5000000
GO
CREATE NONCLUSTERED INDEX IX_NonFilteredIndex1
	ON dbo.Orders (CustomerId) 
GO

/*Let us create one more Table with clustered and 
a non clustered index without any filter.*/
CREATE TABLE dbo.Customers (
  CustomerId int IDENTITY (1, 1) 
           PRIMARY KEY CLUSTERED NOT NULL,
  FirstName Varchar(50),
  LastName Varchar(50))
GO
CREATE NONCLUSTERED INDEX IX_NonFilteredIndex2
  ON dbo.Customers(FirstName, LastName)
GO

Now let us run the query to get the list of all the filtered indexes or all the tables having filtered indexes in Sql Server and verify the result:
How_To_Find_All_Filtered_Indexes_Or_Tables_With_Filtered_Index