Here is a script to check unused indexes in your Sql Server 2005 databases. Once one particular index has been used, the dm_db_index_usage_stats will be updated. All this information will be lost every time Sql Server instance is cycled. Therefore, you should run it after Sql Server has been running for a while, as it may take some time for your users or processes to use the indexes. Remember to replace MyDb with your database name

use MyDb
SELECT OBJECT_NAME(object_id) as TableName, object_name(index_id) as IndexName
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0
and database_id = db_id(‘MyDb’)

I have tried to use sp_spaceused to get out how much storage those unused indexes take, but was not successful so far.

The script below can show you index fragmentation in your database, sorted in descending order. Once again, remember to replace MyDb with your database name.
use MyDb
SELECT object_name(b.object_id) as TableName, b.name as IndexName, a.avg_fragmentation_in_percent FROM
sys.dm_db_index_physical_stats(db_id(‘MyDb’), NULL, NULL, NULL, NULL) a
inner join
sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id order by 3 desc;

This is an example script that generates index reorg script.

SELECT ‘alter index [‘ + b.name + ‘] on [‘ + object_name(b.object_id) + ‘] reorganize’ FROM
sys.dm_db_index_physical_stats(db_id(‘MyDb’), NULL, NULL, NULL, NULL) a
inner join
sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id and b.name is not null where a.avg_fragmentation_in_percent > 50 order by a.index_id, a.avg_fragmentation_in_percent;