How i can DeFrage index of whole database(one database) in

Reha,

[quoted text, click to view]

You cannot do it with a single statement. You can open a cursor of
table names and execute dbcc in a loop, once for each table.

Linda

I'd seriously question your need to do such a thing in all but rare
instances.

Here's a script to do it but Please, read this first.

There is a lot more to indexes than just adding them when things run slow
and reindexing them when you ShowContig starts getting ugly. In most
instance, indexes don't fragment at the same speed. I have some indexes on
my DB that need maintenance weekly at best, and others that have never
needed reindexed in the last year and a half. Reindexing locks tables, so
make sure you have a Window to do this in. I can't tell you how long, it
depends on your schema. Very Very doubtful though that everythying will
need reindexed at the same time, unless this is a one time deal and you've
never re-indexed things.

Second, the 90 is arbitrary, pulled out of the air. The more inserts you
have, the more this number needs tweaked. Don't have a magic number for
you, it really depends on your DB.

Backup and Restore and Performance tuning is something No DBA takes lightly,
and it's not to add job security, it's just that it can have a very big
impact on the DB. If you know all of this, I apologize for the lecture if
it sounds like one. If you don't, Trust me as someone who's screwed up my
share of times, be very careful and make sure you know the intricacies of
indexes before using a script like this.

Hopefully this helps, and like I said, I don't mean to lecture, just don't
want to see anyone screw up.

Cheers,

Bill

Declare @TableName varchar(255)

Declare TableCursor CURSOR for
Select table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName, '', 90) ----------------Pay attention to
this number
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

exec sp_updatestats

GO
[quoted text, click to view]