Friday, April 5, 2013

Tsql script to see creation anf modification time of all indexes in database.

Next script will show the modification date of all indexes in the selected database.

Select,, t.create_date, t.modify_date,,
From sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
             ic.column_id = c.column_id
Where i.index_id > 0   
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key = 0 -- do not include PK indexes
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0
Order by 4 desc


Unknown said...

I want see modification time for views

Unknown said...
This comment has been removed by the author.