Database

How to check the index is disabled or enabled

By muhkam01 | Published March 20, 2023 | No Comments

In this article, we will show you two ways to check whether an index is disabled or enabled.

The first method to check the index status in SQL Server is going through the SQL Server management studio (ssms). Connect to SQL instance using ssms, expand your database under the databases folder, then expand particular table under the tables folder for which you want to check it’s index status. Expand indexes folder and right click on the index and select properties as shown below.

A new window will pop up showing all the properties for that particular index along with the index status as shown in the below screenshot.

Another way to check the index status is to run the following TSQL query in the ssms.

SELECT   name AS [Index_Name]

              , type_desc AS [Index_Type]

              , index_id AS [Index_ID]

              , CASE IS_DISABLED

                           WHEN 0 THEN ‘Enabled’

                           ELSE ‘Disabled’

                     END AS [Index Status]

              , FILL_FACTOR AS [Fill_Factor]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID(‘eligibility’)

GO

After executing the above query, you will see the details of all the indexes for the table mentioned in the query. The result will be something like below.

Latest posts