how to validate if an Index has been disabled?

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  March 23, 2007 at 12:28 pm

  #116535

  while looking over my indexes... (after which i have already disabled)

  i couldn't find a way to see if they were disabled or not.

  how do you validate if an index has been disabled?

  any ideas would be helpful.

  thanks in advance.

  _________________________

• Mark Shvarts 1

  SSCertifiable

  Points: 5702

  More actions

  March 23, 2007 at 3:19 pm

  #696710

  select

  * from sys.indexes

  where

  is_disabled = 1

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  March 23, 2007 at 5:25 pm

  #696724

  cool thanks

  better yet though...

  select sys.objects.name as 'table',

  sys.indexes.name as 'index',

  is_disabled = case is_disabled

  when '1' then 'disabled'

  when '0' then 'enabled'

  end

  from sys.objects join sys.indexes

  on sys.objects.object_id = sys.indexes.object_id

  where sys.objects.name = 'mytable'

  _________________________

• christian 52014

  SSC Journeyman

  Points: 90

  More actions

  November 2, 2018 at 8:28 am

  #2012196

  I found this more practical:

  select sys.objects.name as 'table',
  sys.indexes.name as 'index',
  status = case is_disabled
  when '1' then 'disabled'
  when '0' then 'enabled'
  end
  from sys.objects join sys.indexes
  on sys.objects.object_id = sys.indexes.object_id
  where sys.objects.name in( 'Table1', 'Table2', 'Table3')
  order by 1, 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 2, 2018 at 10:28 am

  #2012225

  christian 52014 - Friday, November 2, 2018 8:28 AM

  I found this more practical:

  select sys.objects.name as 'table',
  sys.indexes.name as 'index',
  status = case is_disabled
  when '1' then 'disabled'
  when '0' then 'enabled'
  end
  from sys.objects join sys.indexes
  on sys.objects.object_id = sys.indexes.object_id
  where sys.objects.name in( 'Table1', 'Table2', 'Table3')
  order by 1, 2

  I have issues with the above query and here is mine as well:
  
-- Issues are commented
select 'table' = [sys].[objects].[name] -- 3 part naming convention has been deprecated
  , 'index' = [sys].[indexes].[name] -- 3 part naming convention has been deprecated
  , 'status' = case [is_disabled]
        when '1' then
         'disabled'
        when '0' then
         'enabled'
      end
from [sys].[objects]
  join [sys].[indexes]
   on [sys].[objects].[object_id] = [sys].[indexes].[object_id]
where [sys].[objects].[name] in ( 'Table1', 'Table2', 'Table3' )
order by 1 -- Use the column name not the ordinal position
   , 2; -- Use the column name not the ordinal position

  -- This is mine:
select
[tab].[name] as [TableName]
, [idx].[name] as [IndexName]
, case [idx].[is_disabled] when 1 then 'disabled' when 0 then 'enabled' else 'Invalid State' end as [Status]
from
[sys].[tables] as [tab] -- only interested in user tables
inner join [sys].[indexes] as [idx]
  on [tab].[object_id] = [idx].[object_id]
where
[tab].[object_id] = object_id('[HumanResources].[Department]')
order by
[tab].[name]
, [idx].[name];