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 s.name, t.name, t.create_date, t.modify_date,i.name, c.name
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

2 comments:

Amine Salaheddine said...

I want see modification time for views

Amine Salaheddine said...
This comment has been removed by the author.