June 22, 2005 at 8:56 pm
Hi All,
Does anyone have this script to rebuild indexes for all tables in all user databases? Please can I have it.
Thanks,
Minh
June 22, 2005 at 9:30 pm
What is wrong with using a maintenance plan to do this ? It requires no code, just run the wizard and your done.
June 23, 2005 at 3:04 am
I use this proc for blanket rebuilds - I also use other more selective methods but this is a handy method. Put the proc in master and call from any database.
create procedure sp_IndexRebuild
-- ============================================================================
-- Stored Procedure: sp_IndexRebuild
-- Written by: Colin Leversuch-Roberts
-- Kelem Consulting Limited
-- http://www.kelemconsulting.co.uk
-- (c) november 2003
--
-- Purpose: To be called from a maint task to enable the rebuilding of indexes on user tables
-- Rebuilds index with current fill factor ( of index )
--
-- System: Master Database
--
-- Input Paramaters none
--
-- Output Parameters: None
-- Return Status: None
--
-- Usage: Exec dbo.sp_IndexRebuild
--
-- Calls: dbcc dbreindex
--
-- Notes: By placing this procedure in master it may be called from any database
-- The procedure will rebuild the user tables in the database the proc is CALLED FROM
-- See also sp_IndexRebuild100 which forces a 100% fillfactor on index rebuild
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 12th Nov 2003 Initial Release
--=======================================================================================
as
set nocount on
declare @table1 table (Numkey int identity(1,1),Tname sysname)
declare @count int, @cmdstring varchar(100)
--
-- look no cursor !!
--
insert into @table1(Tname)
select name from dbo.sysobjects with (nolock) where xtype='U'
set @count=1
while @count<=(select max(Numkey) from @table1)
BEGIN
select @cmdstring=Tname from @table1 where numkey=@count
print @cmdstring
dbcc dbreindex(@cmdstring)
set @count=@count+1
END
--endwhile
GO
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 23, 2005 at 7:27 am
I believe I got this script somewhere on this site, but it cycles through the indexes and checks fragmentation. Depending on the fragmentation it does nothing, runs a rebuild or a reindex. It's fast & effecient. Put it in a stored proc in a database. I keep meaning to modify it to run on a list of databases, but haven't gotten around to it yet.
-- declare variables:
DECLARE
@table_name VARCHAR(255),
@index_name VARCHAR
(255),
@sql VARCHAR
(4000)
-- temporary table to hold
-- intermediate values:
CREATE
TABLE #table_index (
table_index_id
INT IDENTITY(1, 1) NOT NULL,
table_name VARCHAR
(255) NULL,
index_name VARCHAR
(255) NULL,
sql_statement VARCHAR
(4000) NULL,
)
/* get all records from sysindexes for all user tables
** except for text columns and columns for the table itself:
** also exclude dtproperties table:
*/
INSERT
#table_index (
table_name
,
index_name
)
SELECT
c.name + '.' +
a
.name AS table_name,
b
.name AS index_name
FROM
sysobjects a
INNER
JOIN sysindexes b
ON
a.id = b.id
AND
b.indid <> 0 -- table itself
AND
b.indid <> 255 -- text column
AND
a.name <> 'dtproperties'
AND
a.type = 'u'
INNER
JOIN sysusers c
ON
c.uid = a.uid
ORDER
BY 1
IF
@@ERROR <> 0
BEGIN
RAISERROR('error occured while populating a temp table', 16, 1)
RETURN
END
-- temp table to hold results of
-- DBCC SHOWCONTIG:
CREATE
TABLE #showcontig_results (
ObjectName VARCHAR
(255) ,
ObjectID
BIGINT ,
IndexName VARCHAR
(255) ,
IndexID
TINYINT ,
[LEVEL]
TINYINT ,
Pages
BIGINT ,
[Rows]
BIGINT ,
MinimumRecordSize
INT,
MaximumRecordSize
INT,
AverageRecordSize
INT,
ForwardedRecords
INT,
Extents
INT,
ExtentSwitches
INT,
AverageFreeBytes
NUMERIC,
AveragePageDensity
NUMERIC,
ScanDensity
INT,
BestCount
INT,
ActualCount
INT,
LogicalFragmentation
NUMERIC,
ExtentFragmentation
NUMERIC)
/* Run DBCC SHOWCONTIG for all user indexes
**
*/
DECLARE
table_index_cursor CURSOR FOR
SELECT
table_name, index_name FROM #table_index
ORDER
BY 1
OPEN
table_index_cursor
FETCH
NEXT FROM table_index_cursor INTO @table_name, @index_name
WHILE
@@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'DBCC SHOWCONTIG(''' + @table_name + ''', ''' +
@index_name
+ ''') WITH TABLERESULTS'
INSERT #showcontig_results
EXEC(@sql)
IF @@ERROR <> 0
BEGIN
EXEC master..xp_logevent 'error occured while determining
running DBCC SHOWCONTIG'
, ERROR
RETURN
END
FETCH NEXT FROM table_index_cursor INTO @table_name, @index_name
END
CLOSE
table_index_cursor
DEALLOCATE
table_index_cursor
UPDATE
#table_index
SET
sql_statement =
'DBCC INDEXDEFRAG(' + db_name() + ', ''' + table_name + ''',''' + index_name + ''')'
FROM
#table_index a INNER JOIN #showcontig_results b
ON
a.index_name = b.IndexName
WHERE
(ScanDensity BETWEEN 61 AND 85)
OR
LogicalFragmentation BETWEEN 16 AND 40
UPDATE
#table_index
SET
sql_statement =
'DBCC DBREINDEX(''' + db_name() + '.' + table_name + ''',''' + index_name + ''')'
FROM
#table_index a INNER JOIN #showcontig_results b
ON
a.index_name = b.IndexName
WHERE
ScanDensity < 61
OR
LogicalFragmentation > 40
IF
@@ERROR <> 0
BEGIN
EXEC master..xp_logevent 'error occured while determining appropriate index needs', ERROR
RETURN
END
/* Next execute the sql statements created by the
** previous portion of the procedure
**
*/
DECLARE
sql_statement CURSOR FOR
SELECT
sql_statement FROM #table_index
WHERE
sql_statement IS NOT NULL
OPEN
sql_statement
FETCH
NEXT FROM sql_statement INTO @sql
WHILE
@@FETCH_STATUS = 0
BEGIN
EXEC(@sql)
IF @@ERROR <> 0
BEGIN
EXEC master..xp_logevent 'error occured while executing
DBCC DBREINDEX or INDEXDEFRAG'
, ERROR
RETURN
END
FETCH NEXT FROM sql_statement INTO @sql
END
CLOSE
sql_statement
DEALLOCATE
sql_statement
-- cleanup:
DROP
TABLE #table_index
DROP
TABLE #showcontig_results
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 25, 2005 at 10:49 pm
I found this script in same site !
USE (What ever the database to be indexed)
--Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
June 26, 2005 at 2:51 pm
Thank you all.
Minh Vu
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply