May 4, 2005 at 9:01 am
Hi ,
do we have any command for reindexing of all the tables not individual table.
ex:
for
DBCC DBREINDEX (table1, 80)
DBCC INDEXDEFRAG (0, 'tablename', 'indexdefrag')
Thanks,
May 4, 2005 at 9:11 am
Use maintenace wizard.
May 4, 2005 at 9:17 am
Try this stored procedure:
CREATE PROC usp_DBCCReindex
AS
/* Declare Variables */
DECLARE @v_table sysname,
@v_SQL NVARCHAR(2000)
/* Declare the Table Cursor (Identity) */
DECLARE c_Tables CURSOR
FAST_FORWARD FOR
SELECT name
FROM sysobjects obj (NOLOCK)
WHERE type = 'U'
OPEN c_Tables
FETCH NEXT FROM c_Tables INTO @v_Table
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @v_SQL = 'DBCC DBREINDEX(' + @v_Table + ')'
-- PRINT @v_SQL
EXEC(@v_SQL)
END -- -2
FETCH NEXT FROM c_Tables INTO @v_Table
END -- While
CLOSE c_Tables
DEALLOCATE c_Tables
GO
Credit where credit is due, I got this from Patrick Dalton's SQL Server 2000 Black Book.
May 4, 2005 at 9:19 am
For Index Defrag:
CREATE PROC usp_DBCCIndexDefrag
AS
SET NOCOUNT ON
/* Declare Variables */
DECLARE @v_table sysname,
@v_Index INT,
@v_SQL NVARCHAR(2000)
/* Declare the Table Cursor */
DECLARE c_Tables CURSOR
FAST_FORWARD FOR
SELECT name
FROM sysobjects obj (NOLOCK)
WHERE type = 'U'
OPEN c_Tables
FETCH NEXT FROM c_Tables INTO @v_Table
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
/* Declare the Index Cursor */
DECLARE c_Indexes CURSOR
READ_ONLY FOR
SELECT idx.indid
FROM sysobjects obj (NOLOCK)
JOIN sysindexes idx (NOLOCK) ON obj.id = idx.id
WHERE obj.name = @v_Table
AND idx.indid > 0
AND idx.indid < 255
OPEN c_Indexes
FETCH NEXT FROM c_Indexes INTO @v_Index
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @v_SQL = 'DBCC INDEXDEFRAG(0,' +
@v_Table + ', ' +
CONVERT(VARCHAR,@v_Index) + ') WITH NO_INFOMSGS'
-- PRINT @v_SQL
EXEC(@v_SQL)
END -- Index -2
FETCH NEXT FROM c_Indexes INTO @v_Index
END -- Index While
CLOSE c_Indexes
DEALLOCATE c_Indexes
END -- Table -2
FETCH NEXT FROM c_Tables INTO @v_Table
END -- Table While
CLOSE c_Tables
DEALLOCATE c_Tables
GO
May 5, 2005 at 6:03 am
How about this:
EXEC sp_MSforeachtable "PRINT '?' DBCC DBREINDEX ( 'yourDatabaseName.?' , '', 80 )"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply