DBCC REINDEX all user tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by david wootton » DBCC REINDEX all user tables

DBCC REINDEX all user tables

Rate Topic

Display Mode

Topic Options

Author

Message

david wootton

Posted Saturday, October 06, 2007 4:04 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 11, 2006 1:30 AM
Points: 26, Visits: 1

Comments posted to this topic are about the item DBCC REINDEX all user tables

Post #407679

tpelegrin

Posted Monday, February 23, 2009 11:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 23, 2009 11:19 AM
Points: 1, Visits: 0

Hi, I have recently try to execute it using SQL Server 2000 but with no success, the erros message is :

Serveur : Msg 207, Niveau 16, État 3, Ligne 24
'origfillfactor' : nom de colonne incorrect.
Serveur : Msg 207, Niveau 16, État 1, Ligne 24
'origfillfactor' : nom de colonne incorrect.

Whistling

Post #662811

davidw

Posted Wednesday, March 11, 2009 6:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 01, 2010 9:11 AM
Points: 2, Visits: 179

Check to make sure you have delared the variable it is complaing about

Post #673197

@STLSQLGuy

Posted Tuesday, November 02, 2010 2:30 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 4:17 PM
Points: 80, Visits: 203

This is still 95% his code but I modified it a little to work with databases that have schemas other than DBO. It also uses the newer 2005 tables instead of SYSOBJECTS. I needed to use this to work with a JD Edwards implementation. Thanks Mr. Wootton.
-------------------------------------------------------------------------------------------------

drop table #tmp

declare @msg varchar(500)

select x.name,
x.object_id AS id,
y.name AS sch
into #tmp
from sys.tables x, sys.schemas y
where type = 'U'
and y.schema_id = x.schema_id
and x.name LIKE 'F%' -----you can omit this line, all my real tables started with F

declare reindexcursor cursor for

select #tmp.name,
sysindexes.origfillfactor,
#tmp.sch
from sysindexes, #tmp
where sysindexes.id = (select #tmp.id
from #tmp
where #tmp.id=sysindexes.id)
group by #tmp.name,sysindexes.origfillfactor,#tmp.sch
having count(#tmp.name) >= 1
order by 1

open reindexcursor

declare @tname varchar(100)
declare @ffact varchar(10)
declare @tsch varchar(10)

fetch next from reindexcursor into @tname,@ffact,@tsch

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

print ' '
SELECT @msg = 'DBCC DBREINDEX ('''+@tsch+'.'+@tname+''', '' '','+(@ffact)+')'
print @msg
exec (@msg)
print ' '

END

FETCH NEXT FROM reindexcursor INTO @tname, @ffact,@tsch

END

CLOSE reindexcursor
DEALLOCATE reindexcursor
GO

thanks, ERH

Post #1014895

SQLRNNR

Posted Tuesday, November 02, 2010 2:33 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 18,853, Visits: 12,437

Nice change and thanks for sharing.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1014900

« Prev Topic | Next Topic »

Permissions