This is a query to find all the indexes in your table including when it was last updated
Find all indexes on all tables
SELECT OBJECT_NAME(OBJECT_ID) AS 'Table Name', [name] as 'Statistic', STATS_DATE(object_id, index_id) AS 'Last Updated Statistics Date' FROM sys.indexes ORDER BY STATS_DATE(object_id, index_id) DESC
Find all indexes on a particular table
SELECT OBJECT_NAME(OBJECT_ID) AS 'Table Name', [name] as 'Statistic', STATS_DATE(object_id, index_id) AS 'Last Updated Statistics Date' FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = 'YourTableName'
This SQL query returning the information about number of rows in the table as well as number of update/insert/delete after the last index has been rebuilt
SELECT 'TABLE ' = substring(sysobjects.name,1,30) , ' INDEX ' = substring(sysindexes.name,1,30) ,sysIndexes.rowcnt, sysindexes.rowmodctr ,[last updated]=STATS_DATE(sysobjects.id, sysindexes.indid) ,user_seeks,user_scans,user_lookups,user_updates FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id AND sysindexes.indid > 0 INNER JOIN sys.dm_db_index_usage_stats iusage ON iusage.object_id = sysobjects.id AND iusage.index_id = sysindexes.indid WHERE sysobjects.xtype = 'U' AND iusage.database_id = (SELECT dbid FROM master.dbo.sysdatabases WHERE [name] = db_name()) ORDER BY sysobjects.name
Leave a Reply