索引信息统计

IF OBJECT_ID('tempdb.dbo.#usefulindex', 'U') IS NULL
BEGIN
CREATE TABLE #usefulindex
(
DBName VARCHAR(100) ,
index_id BIGINT ,
TableName VARCHAR(100) ,
IndexName VARCHAR(100) ,
type_desc VARCHAR(100) ,
user_seeks BIGINT ,
user_scans BIGINT ,
user_lookups BIGINT ,
user_updates BIGINT ,
user_total BIGINT ,
last_system_seek DATETIME ,
last_user_seek DATETIME ,
last_user_scan DATETIME ,
last_user_lookup DATETIME ,
last_user_update DATETIME ,
queryTime DATETIME ,
tableCreateTime DATETIME
)
END
EXEC sp_msforeachdb '
declare @dbname varchar(200);
select @dbname=''?''
if (@dbname not in (''dbcenter'') and db_id(@dbname)>4)
begin
execute
(
''
use ''+ @dbname+'';
insert into #usefulindex
select ''''''+ @dbname+'''''',
ind.index_id ,
obj.name AS TableName ,
ind.name AS IndexName ,
ind.type_desc ,
indUsage.user_seeks ,
indUsage.user_scans ,
indUsage.user_lookups ,
indUsage.user_updates ,
indUsage.user_seeks+indUsage.user_scans+indUsage.user_lookups,
indUsage.last_system_seek ,
indUsage.last_user_seek,
indUsage.last_user_scan,
indUsage.last_user_lookup,
indUsage.last_user_update,
getdate(),
obj.create_date
FROM ''+ @dbname + ''.sys.indexes AS ind
INNER JOIN ''+ @dbname + ''.sys.objects AS obj ON ind.object_id = obj.object_id
LEFT JOIN ''+ @dbname + ''.sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id AND indUsage.database_id=DB_ID(''''''+ @dbname+'''''')
AND ind.index_id = indUsage.index_id
WHERE ind.type_desc <> ''''HEAP'''' AND obj.type <> ''''S'''' and is_ms_shipped=0 and ind.type_desc <>''''CLUSTERED'''' AND OBJECTPROPERTY(obj.object_id, ''''isusertable'''') = 1
ORDER BY obj.name , ind.name
''
)
end
'

INSERT INTO backuptemp.dbo.usefulindex_1
SELECT *
FROM #usefulindex

DROP TABLE #usefulindex