by kentdean (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 21st June 2001
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Rebuilds all indexes in a SQL Server 7.0 Database
VBcoders
Declare @Table varchar(100)
Declare @Index varchar(100)
Declare @FillFactor Int
DECLARE curIndex CURSOR LOCAL SCROLL
FOR
SELECT CONVERT(Varchar, DB_NAME())
+ '.dbo.' + CONVERT(varchar, sysobjects.name)
AS sIndexTable, sysindexes.name AS sIndexName,
sysindexes.OrigFillFactor AS iIndexFillFactor
FROM sysobjects INNER JOIN
sysindexes ON sysobjects.id = sysindexes.id INNER JOIN
sysindexkeys ON sysindexes.indid = sysindexkeys.indid AND
sysindexes.id = sysindexkeys.id INNER JOIN
INFORMATION_SCHEMA.COLUMNS ON
sysobjects.name = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
AND
sysindexkeys.colid = INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
OPEN curIndex
FETCH FIRST FROM curIndex
INTO @Table, @Index, @FillFactor
WHILE @@FETCH_STATUS=0
BEGIN
DBCC DBREINDEX (@Table,@Index,@FillFactor)
FETCH NEXT FROM curIndex
INTO @Table, @Index, @FillFactor
END
Close curIndex
Deallocate curIndex