Binaryworld - Defragment of Indexes ... [ SQL Server -> Stored Procedure ]

/*
You can defragment all the indexes on all the tables in your database
periodically (for example, one time per week at Sunday) to reduce
fragmentation. The DBCC INDEXDEFRAG statement cannot automatically
defragment all indexes on all the tables in a database; it can only
work on one table and one index at a time. You can use the script
below to defragment all indexes in every table in the current database.
*/

DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) +
'of the ' + rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG (0, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch