This blog has moved, permanently, to http://software.safish.com.

Friday, November 12, 2010

Rebuilding SQL Server Indexes

I've always used cursors to rebuild indexes in SQL Server, like so:

USE YourDatabase

DECLARE @tableName varchar(255)
DECLARE cur CURSOR FOR

SELECT table_schema + '.' + table_name 
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN cur

FETCH NEXT FROM cur INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
	DBCC DBREINDEX(@tableName,' ',90)
	FETCH NEXT FROM cur INTO @tableName
END

CLOSE cur
DEALLOCATE cur

However, I discovered a much easier way to do it today:

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

This is an undocumented stored procedure that I didn't even know existed until this morning. The cursor route is still useful for when you want to exclude tables, but dang....I wish I'd known of this earlier.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.