use dbdocs; -- Replace it with your Application database select object_schema_name(i.id) object_schema, tbls.name as table_name, i.name as index_name, i.indid, i.rowmodctr as modified_rows, i.rowcnt, convert(decimal(18,8), i.rowmodctr / i.rowcnt) as modified_percent, stats_date(i.id, i.indid) as last_stats_update from sysindexes i inner join sysobjects tbls on i.id = tbls.id inner join sysusers schemas on tbls.uid = schemas.uid inner join information_schema.tables tl on tbls.name = tl.table_name and schemas.name = tl.table_schema and tl.table_type = 'BASE TABLE' where 0 < i.indid and tl.table_schema <> 'sys' and i.rowmodctr <> 0 and i.rowcnt <> 0 order by modified_percent desc;
Column | Description ----------------- | ------------------------------------------------------------------ object_schema | Object name. table_name | Table name. index_name | Index name. indid | Index ID. rows_modified | Number of rows modified. row_count | Total number of rows. modified_percent | Percentage of rows modified. last_stats_update | Date of last statistics update.