optimize your database performance

I was reading about improving DB performances and saw this interesting post

http://www.milkcarton.com/blog/2009/01/29/Have+You+Reindexed+Your+Database+Lately.aspx

unfortunately the script for SQL 2005 on that post does not work so here it is a working version

USE DatabaseName –Enter the name of the database you want to reindex
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
declare @com varchar(200)
SET @com = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REORGANIZE’
exec (@com)
print @com
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

Advertisements
Categories: SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s