This T-SQL sample script illustrates how to check index fragmentation of a specified database in SQL Server.  To download the script, please visit http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043

 

Scenarios

This script can be used for checking index fragmentation percentage of tables in SQL Server user databases. You can specify the database name and fragmentation percent, after you run the script, you will get the result.

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

Copy code
SQL
Edit|Remove
SELECT OBJECT_NAME(ind.OBJECT_IDAS TableNameind.name AS IndexNameindexstats.index_type_desc AS IndexTypeindexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULLNULLNULLNULLindexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

 

Last edited Nov 10, 2013 at 7:37 AM by msftdemo, version 2