Defrag/Reindex your database using Alter Index Script--SQL Server 2005

http://dsarvan.wordpress.com/
/*

Purpose : This procedure would do the following

Defragmentation of Index If fragmentation > 5% and <= 9.9%
Reindex of the Index If fragmentation > 19.9%
Update Stats of Indexes If fragmentation > 5%
*/
if OBJECT_ID(N'dba_Defrag_Reindex_StatsUpdate') IS NOT NULL
Drop PROC dba_Defrag_Reindex_StatsUpdate
GO
CREATE PROCEDURE [dbo].[dba_Defrag_Reindex_StatsUpdate]
AS
BEGIN
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @fragmentthreshold DECIMAL
DECLARE @Indexname varchar(255)

SET @fragmentthreshold = 9.9 -- Set the fragmentation Threshold below which the indexes would be defragged and above it would be reindexed

-- Declare cursor and select the list of tables to Defrag or Reindex
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

/*
Defragmentation procedure starts here
*/

-- Declare cursor for list of indexes to be defragged
DECLARE Defragindexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, IndexName, LogicalFrag
FROM #fraglist
WHERE LogicalFrag Between 5.00 And @fragmentthreshold
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN Defragindexes

-- loop through the indexes
FETCH NEXT
FROM Defragindexes
INTO @tablename, @objectid, @indexid, @IndexName, @frag
print @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IndexName) + ' ON dbo.' + LTRIM(RTRIM(@tablename)) + ' REORGANIZE '
PRINT Str(@frag) + '% - ' + @execstr
EXEC (@execstr)
FETCH NEXT
FROM Defragindexes
INTO @tablename, @objectid, @indexid, @IndexName, @frag
END

-- Close and deallocate the cursor
CLOSE Defragindexes
DEALLOCATE Defragindexes

/*
Reindex procedure starts here
*/

-- Declare cursor for list of indexes to be Reindexed
DECLARE Reindexes CURSOR FOR
SELECT ObjectName, IndexName, LogicalFrag
FROM #fraglist
WHERE LogicalFrag > @fragmentthreshold
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN Reindexes

-- loop through the indexes
FETCH NEXT
FROM Reindexes
INTO @tablename, @indexname, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'ALTER INDEX ' + rtrim(@indexname) + ' ON dbo.' + LTRIM(RTRIM(@tablename)) + ' REBUILD WITH (ALLOW_ROW_LOCKS=OFF, SORT_IN_TEMPDB = ON)'
PRINT Str(@frag) + '% - ' + @execstr
EXEC (@execstr)
FETCH NEXT
FROM Reindexes
INTO @tablename, @indexname, @frag
END

-- Close and deallocate the cursor
CLOSE Reindexes
DEALLOCATE Reindexes

/*
Update the Statistics for the Indexes that has been defragmented or reindexed
*/

exec sp_updatestats @resample = 'resample' -- SQL Server would determine if any Statistics need to be updated and it would do that automatically.

SET NOCOUNT OFF
END