code for re-index

hi,
SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql code.
DECLARE db_cursor CURSOR
FOR
SELECT name FROM master..sysdatabases where name not like 'test%' and
dbid>4 order by 1
OPEN db_cursor
......
DECLARE @.dbname sysname
set @.sql = 'use ' + @.dbname + char(13) + 'GO'
exec ( @.SQL)
I declare table cursor to run the reindex ...
It's re-indexing the same db(first one)... don't know why?
any ideas?
ThanksHi
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
Note it uses undocumented stored procedure just be aware
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||Thanks,
How do I find out if it's running table by table... Can I print something>
Thanjks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi
> EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
> Note it uses undocumented stored procedure just be aware
>
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
>|||How about below?
EXEC sp_msForEachTable @.COMMAND1= 'PRINT ''?'' DBCC DBREINDEX ( "?") PRINT '
' '''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:Ol0FSnMuHHA.1052@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
> Thanks,
> How do I find out if it's running table by table... Can I print something>
>
> Thanjks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||works now--thanks a lot
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||See BOL for DBCC SHOWCONTIG. There is a script there for what you want to
do.
TheSQLGuru
President
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>