• 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