pb648174 (google (AT) webpaul (DOT) net) writes:
If the database is that small, I would set up job that just loops
sysobjects and reindex every table in sight. It's not worth the effort
to anything more sophisticated.

The reason that we exempt some tables is from reindexing is mainly to
reduce execution time for the job.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ok, does running the below once per week seem reasonable for a SQL 2005
DB?

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT top 1 table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Exec('ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR =
80)')
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

DBCC CHECKDB
go

pb648174 (google (AT) webpaul (DOT) net) writes:
Some small modifications:

1) The TOP 1 should probably not be there.

2) Change "table_name" to quotename(table_name), so you can defrag
Northwind and its Order Details too. :-)

3) And change to correct case, in case you one day want to run it on
case-sensitive database. It's INFORMATION_SCHEMA etc.

And I will have to admit that I have not really digested the new
ALTER INDEX syntax in SQL 2005, but I guess that part looks find.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx