Defrag Tables

--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