DBCC REINDEX all user tables

Question

Post reply

DBCC REINDEX all user tables

david wootton

Old Hand

Points: 338
More actions
October 6, 2007 at 4:04 am

#179904

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

tpelegrin Valued Member Points: 55 More actions · Answer 1

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:

davidw SSC Enthusiast Points: 128 More actions · Answer 2

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

@STLSQLGuy Default port Points: 1476 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Nice change and thanks for sharing.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events