Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC REINDEX all user tables Expand / Collapse
Author
Message
Posted Saturday, October 6, 2007 4:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, February 23, 2009 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.

Post #662811
Posted Wednesday, March 11, 2009 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 1, 2010 9:11 AM
Points: 2, Visits: 179
Check to make sure you have delared the variable it is complaing about
Post #673197
Posted Tuesday, November 2, 2010 2:30 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 11:03 AM
Points: 80, Visits: 204
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
Posted Tuesday, November 2, 2010 2:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 21,246, Visits: 14,948
Nice change and thanks for sharing.



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


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 »

Add to briefcase

Permissions Expand / Collapse