sqlserver的日常检查脚本归结

核心提示：where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1and i.object_id = s.object_idand i.index_id = s.index_idorder by (user_seeks

where database_id = @dbid 

and objectproperty(s.object_id,'IsUserTable') = 1

and i.object_id = s.object_id

and i.index_id = s.index_id

order by (user_seeks + user_scans + user_lookups + user_updates) asc

go

print '                             '

print '                             '

print '                             '

print '----------------------------'

print '4.3所有数据库未使用的索引  '

print '----------------------------'

print '*********************************'

SELECT TOP 1

        DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

        ,user_updates    

        ,system_updates    

        -- Useful fields below:

        --, *

INTO #TempUnusedIndexes

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id 

WHERE  s.database_id = DB_ID()

    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

    AND    user_seeks = 0

    AND user_scans = 0 

    AND user_lookups = 0

    AND s.[object_id] = -999  -- Dummy value to get table structure.

;

-- Loop around all the databases on the server.

EXEC sp_MSForEachDB    'USE [?]; 

-- Table already exists.

INSERT INTO #TempUnusedIndexes 

SELECT TOP 10    

        DatabaseName = DB_NAME()

        ,TableName = OBJECT_NAME(s.[object_id])

        ,IndexName = i.name

        ,user_updates    

        ,system_updates    

FROM   sys.dm_db_index_usage_stats s 

INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 

    AND s.index_id = i.index_id 

WHERE  s.database_id = DB_ID()

    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0

    AND    user_seeks = 0

    AND user_scans = 0 

    AND user_lookups = 0

    AND i.name IS NOT NULL    -- Ignore HEAP indexes.