André Krijnen

Tag: stats

Stored procedure for Reindexing and Update Stats

by 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.

  1.  
  2. CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
  3. AS
  4. BEGIN
  5. — SET NOCOUNT ON added to prevent extra result sets from
  6. — interfering with SELECT statements.
  7. SET NOCOUNT ON;
  8. DECLARE @MyTable VARCHAR(255)
  9. DECLARE myCursor
  10. CURSOR FOR
  11. SELECT table_name
  12. FROM information_schema.tables
  13. WHERE table_type = 'base table'
  14. OPEN myCursor
  15. FETCH NEXT
  16. FROM myCursor INTO @MyTable
  17. WHILE @@FETCH_STATUS = 0
  18. BEGIN
  19. PRINT 'Reindexing Table:  ' + @MyTable
  20. DBCC DBREINDEX(@MyTable, '', 80)
  21. FETCH NEXT
  22. FROM myCursor INTO @MyTable
  23. END
  24. CLOSE myCursor
  25. DEALLOCATE myCursor
  26. EXEC SP_UPDATESTATS
  27. GO
  28. END
Leave a Comment :, , , , , , , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Blogroll

A few highly recommended websites...