reindex versus maintenance plan

Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
with
> thing
>|||Here is what I use to dbcc indexDefrag all indexes each night:
-- ****************************************
*******************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
-- ****************************************
*******************************
cheers,
Greg Jackson
PDX, Oregon|||I have been looking for a resolution to this as well. Thank you for this in
formation! I have another question - how do I write this if I need to do a
fill factor of 90?
-- Vinodk wrote: --
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing[vbcol=seagreen]
> in a script without using DDBC REINDEX for every table?