my code stock.com

Procedure which rebuild all indexes from a Database.

Snippet options

Download: Download snippet as rebuild-indexes.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

/****** Object:  StoredProcedure [dbo].[usp_rebuildIndexes]    Script Date: 04/25/2012 14:10:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* ***************************************************************************************************
 * Project Name:          	THOT
 * Project Version:      	1
 * Creation Date:         	25 april 2012
 * Author:                	dfe
 * General Description:		$Description$ (don't forget to create MS_Description extended property)
Procedure which rebuild all indexes from a Database.
 * References:http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/            	
 * ====================================================================================================
 * MODIFICATIONS
 * ----------------------------------------------------------------------------------------------------
 * Date            	Author        			Description			Project id
 * ----------------------------------------------------------------------------------------------------
 * Please complete after each code modification.
 * <date>          	<author>      			<fix id>			<documentation id>
 * <description of changes>
 * ************************************************************************************************* */
CREATE PROCEDURE [dbo].[usp_rebuildIndexes]
	@DatabaseName nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @DB_NAME nvarchar(50)
	DECLARE @PROC_NAME nvarchar(100)
	SELECT 
		@DB_NAME = DB_NAME()
	SELECT 
		@PROC_NAME = OBJECT_NAME(@@PROCID)
	
	PRINT('Execute ' + @PROC_NAME)
	
		
	DECLARE @Database VARCHAR(255)  
	DECLARE @Table VARCHAR(255)  
	DECLARE @cmd NVARCHAR(500)  
	DECLARE @fillfactor INT

	SET @fillfactor = 90

	DECLARE DatabaseCursor CURSOR FOR  
	SELECT name FROM MASTER.dbo.sysdatabases  
	WHERE name NOT IN ('master','msdb','tempdb','model','distribution') 
	AND name = @DatabaseName
	ORDER BY 1  

	IF (SELECT CURSOR_STATUS('global','DatabaseCursor')) >= 0 
	BEGIN
		CLOSE DatabaseCursor
		DEALLOCATE DatabaseCursor
	END

	OPEN DatabaseCursor  

	FETCH NEXT FROM DatabaseCursor INTO @Database  
	WHILE @@FETCH_STATUS = 0  
	BEGIN  
		PRINT ('Checks indexes on database ' + @Database)
		
	   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
	  table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
	  WHERE table_type = ''BASE TABLE'''  

	   -- create table cursor  
	   EXEC (@cmd)  
	   OPEN TableCursor  

	   FETCH NEXT FROM TableCursor INTO @Table  
	   WHILE @@FETCH_STATUS = 0  
	   BEGIN  
			PRINT ('Rebuild index on table  ' + @Table)

		   IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
		   BEGIN
			   -- SQL 2005 or higher command
			   SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
			   EXEC (@cmd)
		   END
		   ELSE
		   BEGIN
			  -- SQL 2000 command
			  DBCC DBREINDEX(@Table,' ',@fillfactor)  
		   END

		   FETCH NEXT FROM TableCursor INTO @Table  
	   END  

	   CLOSE TableCursor  
	   DEALLOCATE TableCursor  

	   FETCH NEXT FROM DatabaseCursor INTO @Database  
	END  
	CLOSE DatabaseCursor  
	DEALLOCATE DatabaseCursor 
	
END

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Procedure which rebuild all indexes from a Database.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_rebuildIndexes'
GO

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.