Defrag Database Tables

--
-- Defrag Tables
--
-- Over a period of time, because of the inserts, updates and deletes, the data
-- and the index pages can get fragmented. Here's a script that can help to
-- defrag the database.
--
-- Usage
-- exec [master].dbo.usp_DefragDatabase @dbname = N'<DATABASE_NAME>';
--
-- http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30023/
--

use [master];

go
if exists(select * from sys.objects where object_id = object_id(N'[dbo].[usp_DefragDatabase]') and type in(N'P', N'PC'))
    begin
        drop procedure dbo.usp_DefragDatabase
    end;
go

use [master];
go

set ansi_nulls, quoted_identifier on;
go

--Re-indexes the specified database
create procedure usp_DefragDatabase
    @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