Stored procedure for Reindexing and Update Stats
by mysticslayer on May.09, 2009, under Programming, sql server
Well I was searching for a Stored Procedure that you can use for reindexing and updating stats, etc.
I didn’t find any nice procedures that gave me the solution I needed, so I’ve read some posts by google, and made the following procedure 🙂
This procedure will loop through all the tables and all indexes and shall Reindex them. When every index on every table is reindexed it will update all the stats on the database.
Of course you can choose to change the fill index from 80 to any other valid percentage.
-
-
CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
-
AS
-
BEGIN
-
— SET NOCOUNT ON added to prevent extra result sets from
-
— interfering with SELECT statements.
-
SET NOCOUNT ON;
-
DECLARE @MyTable VARCHAR(255)
-
DECLARE myCursor
-
CURSOR FOR
-
SELECT table_name
-
FROM information_schema.tables
-
WHERE table_type = 'base table'
-
OPEN myCursor
-
FETCH NEXT
-
FROM myCursor INTO @MyTable
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
PRINT 'Reindexing Table: ' + @MyTable
-
DBCC DBREINDEX(@MyTable, '', 80)
-
FETCH NEXT
-
FROM myCursor INTO @MyTable
-
END
-
CLOSE myCursor
-
DEALLOCATE myCursor
-
EXEC SP_UPDATESTATS
-
GO
-
END
