SQL Database Defrag

				
--**************************************
-- Name: SQL Database Defrag
-- Description:This code will organise the indexes on your database to improve speed. (Simple)
-- By: philip hannent
--
-- Assumes:When you run this code it will create a stored proc. Once you have done that just run 
EXEC usp_DefragDatabase 'Test' 
This will perform the defrag.
--
-- Side Effects:While the defrag is in process you performace will take a dive.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=275&lngWId=5--for details.--**************************************

--Re-indexes the specified database
CREATE PROCEDURE usp_DefragDatabase
-- We don't use sysname because it might not be long enough. 
-- sysname is 128 chars, so we use double that. 
@dbname nvarchar(256)
AS 
BEGIN
-- Quote the database name with brackets
DECLARE @quoteddbname nvarchar(256)
set @quoteddbname = quotename( @dbname )
-- The outer EXEC is so we can do USE, not allowed in stored procs
-- The inner EXEC does the actual reindex on each table in the
-- specified database
EXEC('USE'+ @quoteddbname +'
DECLARE @sTableName sysname
DECLARE PKMS_Tables CURSOR LOCAL FOR 
select table_name from information_schema.tables 
where table_type = ''base table'' order by 1 
OPEN PKMS_Tables 
FETCH NEXT FROM PKMS_Tables INTO @sTableName 
WHILE @@FETCH_STATUS = 0 
BEGIN 
select @sTablename = quotename(@sTablename, ''[]'')
EXEC('' DBCC DBREINDEX ( ''+@sTableName+'') WITH NO_INFOMSGS'')
FETCH NEXT FROM PKMS_Tables INTO @sTableName 
END 
CLOSE PKMS_Tables')
END
GO