March 23, 2007 at 12:28 pm
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.
_________________________
March 23, 2007 at 3:19 pm
select
* from sys.indexes
where
is_disabled = 1
March 23, 2007 at 5:25 pm
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'
_________________________
November 2, 2018 at 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
November 2, 2018 at 10:28 am
christian 52014 - Friday, November 2, 2018 8:28 AMI 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];
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply