Analyze index fragmentation in your MSSQL database

fragmented-indexes.sql

SELECT 
  DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
 ,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
 ,SI.NAME AS IndexName
 ,DPS.INDEX_TYPE_DESC AS IndexType
 ,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
 ,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI 
    ON DPS.OBJECT_ID = SI.ID 
    AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC

rebuild-indexes.sql

-- rebuild one:
ALTER INDEX [indexname] ON [table] REBUILD;

-- or rebuild everything:
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)";
 
-- but read this stuff first:
-- http://technet.microsoft.com/en-us/library/ms189858.aspx
-- http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/