I pulled these scripts from the Microsoft 70-431 training kit:
This script allows you to view the fragmentation
DECLARE @dbname VARCHAR(20)
SET @dbname = 'AdventureWorks'
SELECT object_name(dt.object_id),si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
WHERE index_id <> 0
)as dt --it does not return info about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
--If avg_fragmentation_in_percent > 10 then the database has External Fragmentation
--If avg_page_space_used_in_percent < 75 then the database has Internal Fragmentation
This script determines if you should rebuild or reorganize based off the authors
recommendation. You can adjust the values.
DECLARE @dbname VARCHAR(20)
SET @dbname = 'AdventureWorks'
--These indexes should be reorganized, not rebuilt
-- Alter Index ... Reorganize
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where avg_page_space_used_in_percent 10 and
avg_fragmentation_in_percent < 15
--These indexes should be rebuilt, not reorganized
-- Alter Index ... Rebuild
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where avg_page_space_used_in_percent 15