DBCC Reindex with Statistics Information and table name optional

,

This script just combines existing scripts in the library related to reindexing tables, show statistics information and just added the table parameter as optional. It is nothing really new but I had a need for it and I thought someone else might as well.

The database name is mandatory, the table name is optional and based upon a LIKE statement.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_defragDatabase]
    @dbname NVARCHAR(256)
    ,@tablename NVARCHAR(128) = NULL
AS 
BEGIN
    --    -- BEGIN TEST HARNESS
    --    EXEC [dbo].[sp_defragDatabase] 'IntelFlex', 'Account'
    --    -- END TEST HARNESS
     
 DECLARE @quoteddbname NVARCHAR(256)
 SET @quoteddbname = QUOTENAME(@dbname)
 
 EXEC
 (
        'USE ' + @quoteddbname + '
            
        DECLARE @sTableName SYSNAME
        DECLARE @TableID INT
        
        DECLARE [BaseTable_Cursor] CURSOR LOCAL FOR 
        SELECT
            table_name 
        FROM
            information_schema.tables 
        WHERE
            table_type = ''base table'' 
            AND table_name LIKE ''%' + @tablename + '%''
        ORDER BY 1 

        OPEN [BaseTable_Cursor] 
        FETCH NEXT FROM [BaseTable_Cursor] INTO @sTableName 

        WHILE @@FETCH_STATUS = 0 
            BEGIN 
                SELECT @sTablename = quotename(@sTablename, ''[]'')
                SELECT @TableID = object_id(@sTablename)
                
                PRINT ''DBCC SHOWCONTIG (BEFORE REINDEX) FOR TABLE :'' + @sTableName + ''-'' + CAST(@TableID AS VARCHAR(10))
                EXEC('' DBCC SHOWCONTIG ( '' + @TableID + '')'')
                                
                PRINT ''DBCC DBREINDEX FOR TABLE :'' + @sTableName
                EXEC('' DBCC DBREINDEX ( '' + @sTableName + '') WITH NO_INFOMSGS '')
 
                PRINT ''DBCC SHOWCONTIG (AFTER REINDEX) FOR TABLE :'' + @sTableName + ''-'' + CAST(@TableID AS VARCHAR(10))
                EXEC('' DBCC SHOWCONTIG ( '' + @TableID + '')'')

                FETCH NEXT FROM [BaseTable_Cursor] INTO @sTableName 
            END 
        CLOSE [BaseTable_Cursor]'
    )
END

Rate

3 (6)

Share

Share

Rate

3 (6)